Selecting eqivalent cell

Finding and selecting cell from the range from one sheet equal to cell

Page 1 of 1

13 Replies - 888 Views - Last Post: 29 October 2008 - 04:45 AM Rate Topic: -----

#1 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Selecting eqivalent cell

Posted 13 October 2008 - 04:36 AM

Hi!
I want to create a code in VB for the following
Enter the value (Text) at any cell from Sheet 1 say C3.
Sheet 2 contents data.
To find cell from Sheet 2 having the same data as enetered in Sheet 1,cell C3.
Kindly help.
With Reagrds.
SHRINIVAS
Is This A Good Question/Topic? 0
  • +

Replies To: Selecting eqivalent cell

#2 jjsaw5  Icon User is offline

  • I must break you
  • member icon

Reputation: 90
  • View blog
  • Posts: 3,060
  • Joined: 04-January 08

Re: Selecting eqivalent cell

Posted 13 October 2008 - 05:09 AM

Ok you want to create this code, what steps have you taken in doing that? Have you made an attempt that this, because it seems like you are asking us to do this for you, and we don't do that here.

We will be more then happy to look at your code, guide you and give you advice but we do not write whole projects.
Was This Post Helpful? 0
  • +
  • -

#3 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Re: Selecting eqivalent cell

Posted 14 October 2008 - 02:09 AM

View Postjjsaw5, on 13 Oct, 2008 - 05:09 AM, said:

Ok you want to create this code, what steps have you taken in doing that? Have you made an attempt that this, because it seems like you are asking us to do this for you, and we don't do that here.

We will be more then happy to look at your code, guide you and give you advice but we do not write whole projects.


OK
I have tried with the following code.Public Sub PiCalc()
Range("B3").Select
ActiveCell.Value = "CONC"
Range("C3").Select
ActiveCell.Value = InputBox("Enter Value") This is text.
Range("D3").Select
ActiveCell.Value = InputBox("Enter Value") This is numeric for further processing with the formula to be entered.
Dim ActCell As String
Sheets("Sheet2").Select
Range("B4:E4").Select
Selection.Find = Sheets("Sheet1").Range("C3").Value

How ever it ends with the selection of sheet but not the cell from the cell.The range "B4:E4"on Sheet 2 does content the value (Text) entered in Cell "C3" on Sheet 1
Kindly help me.
With best regards
Shrinivas
Was This Post Helpful? 0
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Selecting eqivalent cell

Posted 14 October 2008 - 04:40 AM

remove the = sign in that find method now it will select the area
Was This Post Helpful? 0
  • +
  • -

#5 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Re: Selecting eqivalent cell

Posted 14 October 2008 - 05:56 AM

View Postthava, on 14 Oct, 2008 - 04:40 AM, said:

remove the = sign in that find method now it will select the area


Thanks a lot.
I tried as per your suggestion. How ever it didn't work.
At first I tried with just removing = sign, it shows X 400 but didn't select cell equal to Sheet 1 Cell "C3"
Then I tried with Find.Sheets but was of no help.
Kindly help.
Regards.
SHRINIVAS
Was This Post Helpful? 0
  • +
  • -

#6 BigThoughts  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-October 08

Re: Selecting eqivalent cell

Posted 16 October 2008 - 08:58 PM

The Find method has many pitfalls, not least of which is that it obeys settings left over from your interactive use of the Find command. A few weeks ago, I needed a method to find a known label in a worksheet, so that I could identify the top row of a table, without benefit of a named range.

So that you can grasp my objective, following is my complete method.

Private Function FindDataLabels( _
		ByRef prngInspDataColLbls As Range, _
		ByRef pwksInspections As Worksheet _
			) As Range

	' ==========================================================================
	'
	' Name:			 FindDataLabels
	'
	' Synopsis:		 Using a list stored in a named range of another sheet,
	'				   locate the contiguous range of cells that contains the
	'				   column labels of the data.
	'
	' Arguments:		prngInspDataColLbls = A Range object that represents a
	'										 block of cells that contain, among
	'										 other things, the column labels.
	'
	'				   pwksInspections	 = A Worksheet object that represents
	'										 the worksheet that contains the
	'										 fire alarm inspection data.
	'
	' Returns:		  A Range object, containing the labels row of the fire
	'				   alarm inspection data.
	'
	' Notes:			1)  Column 5 of range prngInspDataColLbls, identified in
	'					   the code by constant DATA_LABELS_TEXT_COLINDEX,
	'					   contains a list of columns, which are expected to be
	'					   in adjacent columns of the same row.
	'
	'				   2)  Sheet wksInspections is searched from the upper left
	'					   corner for the first label. The adjacent columns are
	'					   compared to the subsequent columns in the list. If
	'					   all appears to be in order, a range, starting at the
	'					   cell where the first label was found, and extending
	'					   for as many columns as there are rows in range
	'					   prngInspDataColLbls, is created and returned.
	'
	'				   3)  By returning a range object containing the entire
	'					   first row, this routine communicates both the origin
	'					   cell and the width of the intended data range to the
	'					   caller.
	'
	' Author:		   David A. Gray, Simple Soft Services, Inc., d/b/a WizardWrx
	'
	'				   This module was developed under contract to LifeProtection
	'				   Maintenance & Consulting, Inc. All copyright and other
	'				   ownership rights rest with the Contractor.
	'
	' Copyright:		(C) 2008, LifeProtection Maintenance & Consulting, Inc.
	'				   Fort Worth, Texas, USA
	'				   http://lmcfire.com/
	'				   All rights reserved world wide.
	'
	' References:
	'
	' Created:		  Thursday, 11 September 2008
	'
	' Maintenance History
	'
	' Date	   Author Synopsis
	' ---------- ------ --------------------------------------------------------
	' 2008/09/11 DAG/WW Initial version created.
	' ==========================================================================

	Const DATA_LABELS_TEXT_COLINDEX As Integer = 5
	Const DATA_LABELS_COL2_RESUME As Integer = 2

	On Error GoTo FindDataLabels_Err

	'   ------------------------------------------------------------------------
	'   If this happens, somebody has tampered with the control worksheets.
	'   ------------------------------------------------------------------------

	If prngInspDataColLbls.Columns.Count < DATA_LABELS_TEXT_COLINDEX Then
		mwwException.Throw ERR_NBR_INV_DATA_LABEL_RNG
	End If
	
	'   ------------------------------------------------------------------------
	'   Read the text of the first label from the control sheet. Quit if the
	'   cell is empty. If it is, somebody has tampered with the control sheet.
	'   ------------------------------------------------------------------------

	Dim strLabelText As String
	strLabelText = prngInspDataColLbls.Cells( _
		RANGE_ORIGIN_CELL_INDEX, _
		DATA_LABELS_TEXT_COLINDEX)

	If strLabelText = vbNullString Then
		mwwException.Throw ERR_NBR_INV_DATA_LABEL_RNG
	End If
	
	'   ------------------------------------------------------------------------
	'   The UsedRange property of a worksheet returns a Range that contains all
	'   the cells in the worksheet that have _ever_ been used, for anything.
	'
	'   Make sure it contains at least as many columns as there are rows defined
	'   in the control table. If not, the two have become out of sync.
	'   ------------------------------------------------------------------------

	Dim rngEverything As Range
	Set rngEverything = pwksInspections.UsedRange
	If rngEverything.Columns.Count < prngInspDataColLbls.Rows.Count Then
		mwwException.Throw ERR_NBR_COL_1_LABEL_NOT_FOUND
	End If

	'   ------------------------------------------------------------------------
	'   The Find method of the Range object enables us to do the same operation
	'   that we might perform in the UI. This is the most efficient way to find
	'   the first data column.
	'   ------------------------------------------------------------------------

	Dim rngCol1Cell As Range
	Set rngCol1Cell = rngEverything.Find( _
		strLabelText, _
		LookIn:=xlValues, _
		SearchOrder:=xlByRows, _
		SearchDirection:=xlNext, _
		MatchCase:=False)

	If rngCol1Cell Is Nothing Then
		mwwException.Throw ERR_NBR_COL_1_LABEL_NOT_FOUND
	End If

	'   ------------------------------------------------------------------------
	'   If all goes well, the Find method returns a Range object containing
	'   exactly one cell. The Row and Column properties of this cell give us the
	'   origin address of the data range, and, implicitly, the addresses of the
	'   remaining column label cells.
	'   ------------------------------------------------------------------------

	Dim lngCol1CellRow As Long
	Dim intCol1CellCol As Integer
	lngCol1CellRow = rngCol1Cell.Row
	intCol1CellCol = rngCol1Cell.Column

	Dim intCurrLabelRowIndex As Integer
	Dim intCurrDataColIndex As Integer
	intCurrDataColIndex = intCol1CellCol
	
	For intCurrLabelRowIndex = DATA_LABELS_COL2_RESUME To prngInspDataColLbls.Rows.Count
		strLabelText = prngInspDataColLbls.Cells( _
			intCurrLabelRowIndex, _
			DATA_LABELS_TEXT_COLINDEX)
		intCurrDataColIndex = intCurrDataColIndex + 1
		If strLabelText <> rngEverything.Cells(lngCol1CellRow, intCurrDataColIndex) Then
			mwwException.Throw _
				ERR_NBR_COL_LABELS_NOT_FOUND, _
				"Label for column " _
					& intCurrLabelRowIndex _
					& " cannot be found."
		End If
	
	Next intCurrLabelRowIndex

	Set FindDataLabels = rngCellsFromRng_P6C( _
		rngEverything, _
		lngCol1CellRow, _
		intCol1CellCol, _
		lngCol1CellRow, _
		intCol1CellCol + prngInspDataColLbls.Rows.Count - 1)

	Set rngEverything = Nothing

FindDataLabels_End:

	Exit Function

FindDataLabels_Err:

	mwwException.Show "FindDataLabels"
	Err.Raise mwwException.Number, _
			  mwwException.Source, _
			  mwwException.Description
	Exit Function   ' This is unreachable, but I leave it, for tiger proofing.

End Function



Take special note of the following snippet.

	Set rngCol1Cell = rngEverything.Find( _
		strLabelText, _
		LookIn:=xlValues, _
		SearchOrder:=xlByRows, _
		SearchDirection:=xlNext, _
		MatchCase:=False)



Finally, as written, it looks to me like your code will confine itself to cells B5 through E5, inclusive. This won't search the whole sheet. To search the whole sheet, I defined a range, rngEverything, which is set equal to a range comprised of all used cells in the worksheet that is passed into the method in its second argument, pwksInspections.

	Dim rngEverything As Range
	Set rngEverything = pwksInspections.UsedRange



The above line is an optimization; it eliminates fruitless searches of cells that are now, and always have been, empty. In most worksheets (even large ones) this is most of the sheet.

View PostShrinivas Kulkarni, on 14 Oct, 2008 - 05:56 AM, said:

View Postthava, on 14 Oct, 2008 - 04:40 AM, said:

remove the = sign in that find method now it will select the area


Thanks a lot.
I tried as per your suggestion. How ever it didn't work.
At first I tried with just removing = sign, it shows X 400 but didn't select cell equal to Sheet 1 Cell "C3"
Then I tried with Find.Sheets but was of no help.
Kindly help.
Regards.
SHRINIVAS
[b]
Was This Post Helpful? 0
  • +
  • -

#7 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Re: Selecting eqivalent cell

Posted 23 October 2008 - 10:48 PM

View PostBigThoughts, on 16 Oct, 2008 - 08:58 PM, said:

The Find method has many pitfalls, not least of which is that it obeys settings left over from your interactive use of the Find command. A few weeks ago, I needed a method to find a known label in a worksheet, so that I could identify the top row of a table, without benefit of a named range.

So that you can grasp my objective, following is my complete method.

Private Function FindDataLabels( _
		ByRef prngInspDataColLbls As Range, _
		ByRef pwksInspections As Worksheet _
			) As Range

	' ==========================================================================
	'
	' Name:			 FindDataLabels
	'
	' Synopsis:		 Using a list stored in a named range of another sheet,
	'				   locate the contiguous range of cells that contains the
	'				   column labels of the data.
	'
	' Arguments:		prngInspDataColLbls = A Range object that represents a
	'										 block of cells that contain, among
	'										 other things, the column labels.
	'
	'				   pwksInspections	 = A Worksheet object that represents
	'										 the worksheet that contains the
	'										 fire alarm inspection data.
	'
	' Returns:		  A Range object, containing the labels row of the fire
	'				   alarm inspection data.
	'
	' Notes:			1)  Column 5 of range prngInspDataColLbls, identified in
	'					   the code by constant DATA_LABELS_TEXT_COLINDEX,
	'					   contains a list of columns, which are expected to be
	'					   in adjacent columns of the same row.
	'
	'				   2)  Sheet wksInspections is searched from the upper left
	'					   corner for the first label. The adjacent columns are
	'					   compared to the subsequent columns in the list. If
	'					   all appears to be in order, a range, starting at the
	'					   cell where the first label was found, and extending
	'					   for as many columns as there are rows in range
	'					   prngInspDataColLbls, is created and returned.
	'
	'				   3)  By returning a range object containing the entire
	'					   first row, this routine communicates both the origin
	'					   cell and the width of the intended data range to the
	'					   caller.
	'
	' Author:		   David A. Gray, Simple Soft Services, Inc., d/b/a WizardWrx
	'
	'				   This module was developed under contract to LifeProtection
	'				   Maintenance & Consulting, Inc. All copyright and other
	'				   ownership rights rest with the Contractor.
	'
	' Copyright:		(C) 2008, LifeProtection Maintenance & Consulting, Inc.
	'				   Fort Worth, Texas, USA
	'				   http://lmcfire.com/
	'				   All rights reserved world wide.
	'
	' References:
	'
	' Created:		  Thursday, 11 September 2008
	'
	' Maintenance History
	'
	' Date	   Author Synopsis
	' ---------- ------ --------------------------------------------------------
	' 2008/09/11 DAG/WW Initial version created.
	' ==========================================================================

	Const DATA_LABELS_TEXT_COLINDEX As Integer = 5
	Const DATA_LABELS_COL2_RESUME As Integer = 2

	On Error GoTo FindDataLabels_Err

	'   ------------------------------------------------------------------------
	'   If this happens, somebody has tampered with the control worksheets.
	'   ------------------------------------------------------------------------

	If prngInspDataColLbls.Columns.Count < DATA_LABELS_TEXT_COLINDEX Then
		mwwException.Throw ERR_NBR_INV_DATA_LABEL_RNG
	End If
	
	'   ------------------------------------------------------------------------
	'   Read the text of the first label from the control sheet. Quit if the
	'   cell is empty. If it is, somebody has tampered with the control sheet.
	'   ------------------------------------------------------------------------

	Dim strLabelText As String
	strLabelText = prngInspDataColLbls.Cells( _
		RANGE_ORIGIN_CELL_INDEX, _
		DATA_LABELS_TEXT_COLINDEX)

	If strLabelText = vbNullString Then
		mwwException.Throw ERR_NBR_INV_DATA_LABEL_RNG
	End If
	
	'   ------------------------------------------------------------------------
	'   The UsedRange property of a worksheet returns a Range that contains all
	'   the cells in the worksheet that have _ever_ been used, for anything.
	'
	'   Make sure it contains at least as many columns as there are rows defined
	'   in the control table. If not, the two have become out of sync.
	'   ------------------------------------------------------------------------

	Dim rngEverything As Range
	Set rngEverything = pwksInspections.UsedRange
	If rngEverything.Columns.Count < prngInspDataColLbls.Rows.Count Then
		mwwException.Throw ERR_NBR_COL_1_LABEL_NOT_FOUND
	End If

	'   ------------------------------------------------------------------------
	'   The Find method of the Range object enables us to do the same operation
	'   that we might perform in the UI. This is the most efficient way to find
	'   the first data column.
	'   ------------------------------------------------------------------------

	Dim rngCol1Cell As Range
	Set rngCol1Cell = rngEverything.Find( _
		strLabelText, _
		LookIn:=xlValues, _
		SearchOrder:=xlByRows, _
		SearchDirection:=xlNext, _
		MatchCase:=False)

	If rngCol1Cell Is Nothing Then
		mwwException.Throw ERR_NBR_COL_1_LABEL_NOT_FOUND
	End If

	'   ------------------------------------------------------------------------
	'   If all goes well, the Find method returns a Range object containing
	'   exactly one cell. The Row and Column properties of this cell give us the
	'   origin address of the data range, and, implicitly, the addresses of the
	'   remaining column label cells.
	'   ------------------------------------------------------------------------

	Dim lngCol1CellRow As Long
	Dim intCol1CellCol As Integer
	lngCol1CellRow = rngCol1Cell.Row
	intCol1CellCol = rngCol1Cell.Column

	Dim intCurrLabelRowIndex As Integer
	Dim intCurrDataColIndex As Integer
	intCurrDataColIndex = intCol1CellCol
	
	For intCurrLabelRowIndex = DATA_LABELS_COL2_RESUME To prngInspDataColLbls.Rows.Count
		strLabelText = prngInspDataColLbls.Cells( _
			intCurrLabelRowIndex, _
			DATA_LABELS_TEXT_COLINDEX)
		intCurrDataColIndex = intCurrDataColIndex + 1
		If strLabelText <> rngEverything.Cells(lngCol1CellRow, intCurrDataColIndex) Then
			mwwException.Throw _
				ERR_NBR_COL_LABELS_NOT_FOUND, _
				"Label for column " _
					& intCurrLabelRowIndex _
					& " cannot be found."
		End If
	
	Next intCurrLabelRowIndex

	Set FindDataLabels = rngCellsFromRng_P6C( _
		rngEverything, _
		lngCol1CellRow, _
		intCol1CellCol, _
		lngCol1CellRow, _
		intCol1CellCol + prngInspDataColLbls.Rows.Count - 1)

	Set rngEverything = Nothing

FindDataLabels_End:

	Exit Function

FindDataLabels_Err:

	mwwException.Show "FindDataLabels"
	Err.Raise mwwException.Number, _
			  mwwException.Source, _
			  mwwException.Description
	Exit Function   ' This is unreachable, but I leave it, for tiger proofing.

End Function



Take special note of the following snippet.

	Set rngCol1Cell = rngEverything.Find( _
		strLabelText, _
		LookIn:=xlValues, _
		SearchOrder:=xlByRows, _
		SearchDirection:=xlNext, _
		MatchCase:=False)



Finally, as written, it looks to me like your code will confine itself to cells B5 through E5, inclusive. This won't search the whole sheet. To search the whole sheet, I defined a range, rngEverything, which is set equal to a range comprised of all used cells in the worksheet that is passed into the method in its second argument, pwksInspections.

	Dim rngEverything As Range
	Set rngEverything = pwksInspections.UsedRange



The above line is an optimization; it eliminates fruitless searches of cells that are now, and always have been, empty. In most worksheets (even large ones) this is most of the sheet.

View PostShrinivas Kulkarni, on 14 Oct, 2008 - 05:56 AM, said:

View Postthava, on 14 Oct, 2008 - 04:40 AM, said:

remove the = sign in that find method now it will select the area


Thanks a lot.
I tried as per your suggestion. How ever it didn't work.
At first I tried with just removing = sign, it shows X 400 but didn't select cell equal to Sheet 1 Cell "C3"
Then I tried with Find.Sheets but was of no help.
Kindly help.
Regards.
SHRINIVAS
[b]

Alright.
To get a more clarity on what exactly I am trying to achieve,attached here with a excel file containing VB code.
On sheet 2 main componants GI32 and GI28 are appearing in row 3 of column C & E.These have subcomponants A through I appearing in rows from 5 to 13 of columns B & D.The values for these individuacl subcoponants are appearing in rows 5 to 13 of column C & E.My objective is to calculate the values of subcomponants when the main componants (GI 32 & GI 28)are used in various percentage.
After clicking the (calculate) button on sheet 1 ,dilog box prompts to enter value.If you enter text GI 28 or GI 32 the dilog box prompts to enter value.Here you may enter any value upto 100.Then code displays the text appearing into the sheet 2 column respecive to GI 28 or GI 32 and calculates the percentage of the each cell for the respective column for the value appearing into the adjesant column and displays on sheet 1 .This works perfectly alright.But my problem is that if I have too many columns I need to repeat the code as many times as many are the columns.For instance in current example since there are only 2 columns GI 28 or GI 32 the codes are repeated twice.If I have to have 25 or 30 such columns then code needs to be repeated that many times.I am trying to find out the way to make the code short so that the same code need not written again and again which can help to minimise the errors.
Hope you can help me out.
Regards and thanks in advance.
Shrinivas
Was This Post Helpful? 0
  • +
  • -

#8 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Re: Selecting eqivalent cell

Posted 23 October 2008 - 11:28 PM

View PostShrinivas Kulkarni, on 23 Oct, 2008 - 10:48 PM, said:

View PostBigThoughts, on 16 Oct, 2008 - 08:58 PM, said:

The Find method has many pitfalls, not least of which is that it obeys settings left over from your interactive use of the Find command. A few weeks ago, I needed a method to find a known label in a worksheet, so that I could identify the top row of a table, without benefit of a named range.

So that you can grasp my objective, following is my complete method.

Private Function FindDataLabels( _
		ByRef prngInspDataColLbls As Range, _
		ByRef pwksInspections As Worksheet _
			) As Range

	' ==========================================================================
	'
	' Name:			 FindDataLabels
	'
	' Synopsis:		 Using a list stored in a named range of another sheet,
	'				   locate the contiguous range of cells that contains the
	'				   column labels of the data.
	'
	' Arguments:		prngInspDataColLbls = A Range object that represents a
	'										 block of cells that contain, among
	'										 other things, the column labels.
	'
	'				   pwksInspections	 = A Worksheet object that represents
	'										 the worksheet that contains the
	'										 fire alarm inspection data.
	'
	' Returns:		  A Range object, containing the labels row of the fire
	'				   alarm inspection data.
	'
	' Notes:			1)  Column 5 of range prngInspDataColLbls, identified in
	'					   the code by constant DATA_LABELS_TEXT_COLINDEX,
	'					   contains a list of columns, which are expected to be
	'					   in adjacent columns of the same row.
	'
	'				   2)  Sheet wksInspections is searched from the upper left
	'					   corner for the first label. The adjacent columns are
	'					   compared to the subsequent columns in the list. If
	'					   all appears to be in order, a range, starting at the
	'					   cell where the first label was found, and extending
	'					   for as many columns as there are rows in range
	'					   prngInspDataColLbls, is created and returned.
	'
	'				   3)  By returning a range object containing the entire
	'					   first row, this routine communicates both the origin
	'					   cell and the width of the intended data range to the
	'					   caller.
	'
	' Author:		   David A. Gray, Simple Soft Services, Inc., d/b/a WizardWrx
	'
	'				   This module was developed under contract to LifeProtection
	'				   Maintenance & Consulting, Inc. All copyright and other
	'				   ownership rights rest with the Contractor.
	'
	' Copyright:		(C) 2008, LifeProtection Maintenance & Consulting, Inc.
	'				   Fort Worth, Texas, USA
	'				   http://lmcfire.com/
	'				   All rights reserved world wide.
	'
	' References:
	'
	' Created:		  Thursday, 11 September 2008
	'
	' Maintenance History
	'
	' Date	   Author Synopsis
	' ---------- ------ --------------------------------------------------------
	' 2008/09/11 DAG/WW Initial version created.
	' ==========================================================================

	Const DATA_LABELS_TEXT_COLINDEX As Integer = 5
	Const DATA_LABELS_COL2_RESUME As Integer = 2

	On Error GoTo FindDataLabels_Err

	'   ------------------------------------------------------------------------
	'   If this happens, somebody has tampered with the control worksheets.
	'   ------------------------------------------------------------------------

	If prngInspDataColLbls.Columns.Count < DATA_LABELS_TEXT_COLINDEX Then
		mwwException.Throw ERR_NBR_INV_DATA_LABEL_RNG
	End If
	
	'   ------------------------------------------------------------------------
	'   Read the text of the first label from the control sheet. Quit if the
	'   cell is empty. If it is, somebody has tampered with the control sheet.
	'   ------------------------------------------------------------------------

	Dim strLabelText As String
	strLabelText = prngInspDataColLbls.Cells( _
		RANGE_ORIGIN_CELL_INDEX, _
		DATA_LABELS_TEXT_COLINDEX)

	If strLabelText = vbNullString Then
		mwwException.Throw ERR_NBR_INV_DATA_LABEL_RNG
	End If
	
	'   ------------------------------------------------------------------------
	'   The UsedRange property of a worksheet returns a Range that contains all
	'   the cells in the worksheet that have _ever_ been used, for anything.
	'
	'   Make sure it contains at least as many columns as there are rows defined
	'   in the control table. If not, the two have become out of sync.
	'   ------------------------------------------------------------------------

	Dim rngEverything As Range
	Set rngEverything = pwksInspections.UsedRange
	If rngEverything.Columns.Count < prngInspDataColLbls.Rows.Count Then
		mwwException.Throw ERR_NBR_COL_1_LABEL_NOT_FOUND
	End If

	'   ------------------------------------------------------------------------
	'   The Find method of the Range object enables us to do the same operation
	'   that we might perform in the UI. This is the most efficient way to find
	'   the first data column.
	'   ------------------------------------------------------------------------

	Dim rngCol1Cell As Range
	Set rngCol1Cell = rngEverything.Find( _
		strLabelText, _
		LookIn:=xlValues, _
		SearchOrder:=xlByRows, _
		SearchDirection:=xlNext, _
		MatchCase:=False)

	If rngCol1Cell Is Nothing Then
		mwwException.Throw ERR_NBR_COL_1_LABEL_NOT_FOUND
	End If

	'   ------------------------------------------------------------------------
	'   If all goes well, the Find method returns a Range object containing
	'   exactly one cell. The Row and Column properties of this cell give us the
	'   origin address of the data range, and, implicitly, the addresses of the
	'   remaining column label cells.
	'   ------------------------------------------------------------------------

	Dim lngCol1CellRow As Long
	Dim intCol1CellCol As Integer
	lngCol1CellRow = rngCol1Cell.Row
	intCol1CellCol = rngCol1Cell.Column

	Dim intCurrLabelRowIndex As Integer
	Dim intCurrDataColIndex As Integer
	intCurrDataColIndex = intCol1CellCol
	
	For intCurrLabelRowIndex = DATA_LABELS_COL2_RESUME To prngInspDataColLbls.Rows.Count
		strLabelText = prngInspDataColLbls.Cells( _
			intCurrLabelRowIndex, _
			DATA_LABELS_TEXT_COLINDEX)
		intCurrDataColIndex = intCurrDataColIndex + 1
		If strLabelText <> rngEverything.Cells(lngCol1CellRow, intCurrDataColIndex) Then
			mwwException.Throw _
				ERR_NBR_COL_LABELS_NOT_FOUND, _
				"Label for column " _
					& intCurrLabelRowIndex _
					& " cannot be found."
		End If
	
	Next intCurrLabelRowIndex

	Set FindDataLabels = rngCellsFromRng_P6C( _
		rngEverything, _
		lngCol1CellRow, _
		intCol1CellCol, _
		lngCol1CellRow, _
		intCol1CellCol + prngInspDataColLbls.Rows.Count - 1)

	Set rngEverything = Nothing

FindDataLabels_End:

	Exit Function

FindDataLabels_Err:

	mwwException.Show "FindDataLabels"
	Err.Raise mwwException.Number, _
			  mwwException.Source, _
			  mwwException.Description
	Exit Function   ' This is unreachable, but I leave it, for tiger proofing.

End Function



Take special note of the following snippet.

	Set rngCol1Cell = rngEverything.Find( _
		strLabelText, _
		LookIn:=xlValues, _
		SearchOrder:=xlByRows, _
		SearchDirection:=xlNext, _
		MatchCase:=False)



Finally, as written, it looks to me like your code will confine itself to cells B5 through E5, inclusive. This won't search the whole sheet. To search the whole sheet, I defined a range, rngEverything, which is set equal to a range comprised of all used cells in the worksheet that is passed into the method in its second argument, pwksInspections.

	Dim rngEverything As Range
	Set rngEverything = pwksInspections.UsedRange



The above line is an optimization; it eliminates fruitless searches of cells that are now, and always have been, empty. In most worksheets (even large ones) this is most of the sheet.

View PostShrinivas Kulkarni, on 14 Oct, 2008 - 05:56 AM, said:

View Postthava, on 14 Oct, 2008 - 04:40 AM, said:

remove the = sign in that find method now it will select the area


Thanks a lot.
I tried as per your suggestion. How ever it didn't work.
At first I tried with just removing = sign, it shows X 400 but didn't select cell equal to Sheet 1 Cell "C3"
Then I tried with Find.Sheets but was of no help.
Kindly help.
Regards.
SHRINIVAS
[b]

Alright.
To get a more clarity on what exactly I am trying to achieve,attached here with a excel file containing VB code.
On sheet 2 main componants GI32 and GI28 are appearing in row 3 of column C & E.These have subcomponants A through I appearing in rows from 5 to 13 of columns B & D.The values for these individuacl subcoponants are appearing in rows 5 to 13 of column C & E.My objective is to calculate the values of subcomponants when the main componants (GI 32 & GI 28)are used in various percentage.
After clicking the (calculate) button on sheet 1 ,dilog box prompts to enter value.If you enter text GI 28 or GI 32 the dilog box prompts to enter value.Here you may enter any value upto 100.Then code displays the text appearing into the sheet 2 column respecive to GI 28 or GI 32 and calculates the percentage of the each cell for the respective column for the value appearing into the adjesant column and displays on sheet 1 .This works perfectly alright.But my problem is that if I have too many columns I need to repeat the code as many times as many are the columns.For instance in current example since there are only 2 columns GI 28 or GI 32 the codes are repeated twice.If I have to have 25 or 30 such columns then code needs to be repeated that many times.I am trying to find out the way to make the code short so that the same code need not written again and again which can help to minimise the errors.
Hope you can help me out.
Regards and thanks in advance.
Shrinivas

Was This Post Helpful? 0
  • +
  • -

#9 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Selecting eqivalent cell

Posted 24 October 2008 - 05:14 AM

hey Where is the File
Was This Post Helpful? 0
  • +
  • -

#10 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Re: Selecting eqivalent cell

Posted 24 October 2008 - 09:59 PM

Sorry.It seems the file didn't get attached.
Here it is.
Uff!! I am getting the following message.
Upload failed. You are not permitted to upload a file with that file extension.[u]
How ever it's an excel file with an file extension of xls.
Kindly suggest how to overcome this problem.
Regards.
Shrinivas
Was This Post Helpful? 0
  • +
  • -

#11 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Selecting eqivalent cell

Posted 24 October 2008 - 10:23 PM

convert to zip file and post here
some one will surely help you
Was This Post Helpful? 0
  • +
  • -

#12 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Re: Selecting eqivalent cell

Posted 26 October 2008 - 04:23 AM

View PostShrinivas Kulkarni, on 24 Oct, 2008 - 09:59 PM, said:

Sorry.It seems the file didn't get attached.
Here it is.
Uff!! I am getting the following message.
Upload failed. You are not permitted to upload a file with that file extension.[u]
How ever it's an excel file with an file extension of xls.
Kindly suggest how to overcome this problem.
Regards.
Shrinivas

As suggested by you attached a zip file.
Omce again I am getting the same error code
Upload failed. You are not permitted to upload a file with that file extension.
Trying to mail it separatly on your e mail id.
Is it ok?
Shrinivas
Was This Post Helpful? 0
  • +
  • -

#13 Shrinivas Kulkarni  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 13-October 08

Re: Selecting eqivalent cell

Posted 26 October 2008 - 11:04 PM

Attached File  CAL.zip (10.17K)
Number of downloads: 42Attached File  CAL.zip (10.17K)
Number of downloads: 42

View Postthava, on 24 Oct, 2008 - 05:14 AM, said:

hey Where is the File

Let me try again.
Hope it's got posted.
Was This Post Helpful? 0
  • +
  • -

#14 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Selecting eqivalent cell

Posted 29 October 2008 - 04:45 AM

i already told that just remove the = sign now
here is a bit of code from your file i just able to give an information only

try to produce your code using the findnext

Dim TempRange As Range
Sheets("Sheet2").Select
Range("B4:E4").Select
Set TempRange = Selection.Find(Sheets("Sheet1").Range("C3").Value)
Debug.Print TempRange.Address


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1