Get Excel cell reference

[StuartDwight]

I have some code below which opens a file in excel and gets a cell value.
What has happened is the file I am looking in changed and can contain a varying no of rows before the row I want.
What I need to do now is find the cell reference that contains a text string "Total Net Pay:", then I know the value I want will be in the next column on that row.
Has anyone got the code I would need that would give me the cell reference.
Thanks in advance,
Stu.


'Start of my code
Dim appExcel As Excel.Application
Dim xlWB As Excel.Workbook
Dim curNetPay As Currency
Set appExcel = New Excel.Application
Set xlWB = appExcel.Workbooks.Open("W:\my.csv")
With xlWB
.Worksheets(1).Select
curNetPay = .ActiveSheet.Range("E24").Value
End With
xlWB.Close
appExcel.Quit
Set xlWB = Nothing
Set appExcel = Nothing

 

[Guus2005]

Perhaps you could try Cells(row, col)
Example:

 .Cells(intX, 2) = .Cells(intX, 2) + wsSheet.Cells(intX, 2)

 

[Brianwarnock]

You will want code like

Worksheets("sheet1").Columns("B:B").Select  ' select the correct column and worksheet
For Each c In Selection
If c.Value = "Total Net Pay" Then
c.Offset(0, 1) =   ' this is the cell in the next column of the row
Go to   ' this will be some sort of exit from this loopEnd If
Next c

Brian

 

[chergh]

I would do it like this:

curnetpay = worksheets("Sheet1").Cells.Find("Total Net Pay", LookIn:=xlValues).Offset(0, 1).Value

 

[Brianwarnock]

I would do it like this:

curnetpay = worksheets("Sheet1").Cells.Find("Total Net Pay", LookIn:=xlValues).Offset(0, 1).Value

Yes that's neat.
I admit to not reading his post properly I thought he was setting a value, tho' your technique works just aswell for that.

Brian

 

[StuartDwight]

Thanks guys, it was the offset command that was my missing link, you have saved me a lot of time. I can get on and finish my coding now.