mixing absolute and relative references
 

Hi everyone.

This is code by JE McGimpsey, modified slightly by me:


Code
-------------------
Sub Find_and_Mark()

Dim c As Range
Dim firstAddress As String

Sheets("Sheet1").Select
With Columns("C:D")
Set c = .Find( _
What:="your_text_string_here", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, 20) = "Yes"
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If

End With
End Su
-------------------


Basically, it searches for a string, and each time it finds anothe
instance of the string, it marks "Yes" 20 columns over. However, I'
doing a search in both column C and in column D, so 20 columns ove
means either column W or column X respectively.

How do I mix relative and absolute references in this case? Fo
example, how would I modify this if I need the row reference to b
relative, but I always want the column to be column X

--
shellshoc
-----------------------------------------------------------------------
shellshock's Profile: http://www.excelforum.com/member.php...fo&userid=2493
View this thread: http://www.excelforum.com/showthread.php?threadid=46760

mixing absolute and relative references
 

You can set a different variable for your row index.

For i = 1 to 50
c.Offset(i, 20)
Next i

"shellshock" wrote:


Hi everyone.

This is code by JE McGimpsey, modified slightly by me:


Code:
--------------------
Sub Find_and_Mark()

Dim c As Range
Dim firstAddress As String

Sheets("Sheet1").Select
With Columns("C:D")
Set c = .Find( _
What:="your_text_string_here", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(0, 20) = "Yes"
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If

End With
End Sub
--------------------


Basically, it searches for a string, and each time it finds another
instance of the string, it marks "Yes" 20 columns over. However, I'm
doing a search in both column C and in column D, so 20 columns over
means either column W or column X respectively.

How do I mix relative and absolute references in this case? For
example, how would I modify this if I need the row reference to be
relative, but I always want the column to be column X?


--
shellshock
------------------------------------------------------------------------
shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935
View this thread: http://www.excelforum.com/showthread...hreadid=467601

mixing absolute and relative references
 

This has been solved by changing this line


Code:
--------------------
c.Offset(0, 20) = "Yes"
--------------------


to


Code:
--------------------
Cells(c.Row, "X") = "Yes"
--------------------


--
shellshock
------------------------------------------------------------------------
shellshock's Profile: http://www.excelforum.com/member.php...o&userid=24935
View this thread: http://www.excelforum.com/showthread...hreadid=467601