View Full Version : Solved: Offset to 1st & 2nd cell in row
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.
.Value = Sh.Cells(3, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
Gil
Aussiebear
05-11-2010, 02:31 PM
Excel requires the following logic for the offset function
OFFSET(reference,rows,cols,height,width)
In your code, what is the reference cell?
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
05-11-2010, 05:14 PM
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:
.Offset(, -ActiveCell.Column + 1)
.Offset(, -ActiveCell.Column + 2)
rbrhodes
05-11-2010, 05:39 PM
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?
With sh
Range("somecell") = .Cells(3, fnd.Column) & "-" & .Cells(3,1) & "-" & .Cells(3,2)
End With
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.
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
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
mdmackillop
05-12-2010, 05:20 AM
Is this what you're after?
.Value = Sh.Cells(3, Fnd.Column) & "-" & Sh.Cells(Fnd.Row, 2) & "-" & Sh.Cells(Fnd.Row, 1)
Hello mdmackillop
Absolutely spot. Works perfectly for me.
Many thanks
Gil
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.