Solved: Offset to 1st & 2nd cell in row

[Gil]

Hello

I use this offset procedure in one of my projects. Is there a way I can make the offset to the first and second cell in the row.

[vba]
.Value = Sh.Cells(3, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
[/vba]
Gil

[Aussiebear]

Excel requires the following logic for the offset function

OFFSET(reference,rows,cols,height,width)

In your code, what is the reference cell?

[Gil]

Hello Aussiebear

Thanks for the reply. I appreciate the points you make. When that line of code is in the project it all runs ok. What I am asking is instead of the offset just looking at -1 or -2 it actually refers back to the 1st & 2nd cells in that row i.e. if on row 7 then it looks at columns A7 and B7.

Gil

[Simon Lloyd]

Where you have omited the Row indicator in Fnd.Offset(, -1)...etc Excel will always assume that the row is the Target or Activecell row. What do you want to acheive?

I think you may want this:
[VBA].Offset(, -ActiveCell.Column + 1)
.Offset(, -ActiveCell.Column + 2)[/VBA]

[rbrhodes]

Hi Gil,

I see you're online right now.

"...the 1st & 2nd cells in that row i.e. if on row 7 then it looks at columns A7 and B7."

I take it 1st and second cells in that row means 1st and second Columns in that row?
[VBA]

With sh

Range("somecell") = .Cells(3, fnd.Column) & "-" & .Cells(3,1) & "-" & .Cells(3,2)

End With

[/VBA]

If the row number was variable 'vRow' it would be .Cells(vRow,1) or .cells(vRow,"A") for Col A , etc

To make it a formula add a "=" & at the beginning...

Standing by.

[Gil]

Hello all
Thank you for the replies. I have tried the first from Simon with some result but it isn't working correctly. I have attached an example worksheet mod1 is how it currently works and mod2 is with Simons input.
Mod2 finds the first result ok but then seems to put a different offset in. I think it has something to do with this line With c.Offset(, 6)
Thank you rbrhodes, not sure if your suggestion is what I am after.

This is the ammended code
[vba]
Private Sub Anotherexample1_Click()
Dim lngLastRow As Long
' This loop runs the following code in column A
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Dim FirstAddress As String
Set Sh = Sheets("Sheet2")

Columns("F:H").ClearContents 'This line is temp to clear old results

Set c = Cells.Find(What:="1/", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not c Is Nothing Then FirstAddress = c.Address
Do
Set Fnd = Sh.Cells.Find((Split(c)(1)), LookAt:=xlWhole)
If Not Fnd Is Nothing Then
With c.Offset(, 6)
' This line is where I applied the suggestion from Simon
.Value = Sh.Cells(3, Fnd.Column - 0) & "-" & Fnd.Offset(, -ActiveCell.Column - 1) & "-" & Fnd.Offset(, -ActiveCell.Column + 0)
.Font.Bold = True
.Font.Color = -16776961
End With

Else
With c.Offset(, 6)
.Value = "Not found"
.Font.Bold = True
.Font.Color = -16776961
End With
End If
Set c = Cells.Find(What:="1/", After:=c, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
Loop While Not c Is Nothing And c.Address <> FirstAddress

Columns("F:F").Select
End Sub
[/vba]

[mdmackillop]

Is this what you're after?
[VBA].Value = Sh.Cells(3, Fnd.Column) & "-" & Sh.Cells(Fnd.Row, 2) & "-" & Sh.Cells(Fnd.Row, 1)[/VBA]

[Gil]

Hello mdmackillop
Absolutely spot. Works perfectly for me.
Many thanks
Gil