The sub below is currently working but is only running once and and exiting the loop, it should continue to loop through column "R" till the current address of "dodCell" is that of the firstaddress and then exit.
Please let me know if you need any more information
Please let me know if you need any more information
Code:
Sub addnumber()
'used to add ree value to Dod projects
Dim sSht As Worksheet, dSht As Worksheet
Dim lastrow As Integer
Dim firstAddress As String
Dim strSearch As String
Dim ReeCell As Range, dodCell As Range, aRange As Range, rRange As Range, aaRange As Range
Dim hold1Cell As Range, holdCell As Range, lastCell As Range
Set sSht = Worksheets("Sheet1")
Set dSht = Worksheets("Sheet2")
Set rRange = sSht.Columns(18)
Set aRange = sSht.Columns(1)
Set aaRange = dSht.Columns(1)
lastrow = sSht.Range("A" & Rows.Count).End(xlUp).Row
strSearch = "2*"
Set dodCell = rRange.Find(What:=strSearch, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'If something dodCell holds a value then enter loop
If Not dodCell Is Nothing Then
'Set lastCell to dodCell
firstAddress = dodCell.Address
Do
'Set ReeCell to the value of the Ree number
Set ReeCell = dodCell.Offset(0, -17)
'Set holdCell to the Cell that holds that Dod number in "Sheet2"
Set holdCell = aaRange.Find(What:=dodCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Set hold1Cell to the location that the Ree Value should be
Set hold1Cell = holdCell.Offset(0, 9)
'Give hold1Cell the Ree # from ReeCell
hold1Cell = ReeCell.Value
Set dodCell = rRange.FindNext(dodCell)
Loop While Not dodCell Is Nothing And dodCell.Address <> firstAddress
End If
End Sub