Hello,
I have t below code and I need to add another range.
For Each c In sh1.Range("AT5", sh1.Range("AT" & Rows.Count).End(xlUp)) - I want to add range ("AR5", sh1.Range("AR" & Rows.Count)
Set f = sh2.Range("O:O").Find(c.Value, , xlValues, xlWhole) - I want to add range ("M:M").Find(c.Value, , xlValues, xlWhole)
I also need to change this part of code. As I update sheets("Wharf Schedules") if no match is found I want cell on sheets("Data") to not change. At present if I update sheets("Wharf Schedules") and the Vessel Name and Voyage Number are not found it deletes data from cell.
'If Not f Is Nothing Then'
Sub Import_Vessel_Availablility_Update()
Dim c As Range, f As Range, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Data")
Set sh2 = Sheets("Wharf Schedules")
For Each c In sh1.Range("AT5", sh1.Range("AT" & Rows.Count).End(xlUp))
If c.Value <> "" Then
Set f = sh2.Range("O:O").Find(c.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
sh1.Range("AU" & c.Row) = sh2.Range("Q" & f.Row)
sh1.Range("AV" & c.Row) = sh2.Range("R" & f.Row)
sh1.Range("AW" & c.Row) = sh2.Range("S" & f.Row)
End If
End If
Next
MsgBox "Vessel Availablility details have been updated in main Data Sheet"
End Sub
Thank you in advance
I have t below code and I need to add another range.
For Each c In sh1.Range("AT5", sh1.Range("AT" & Rows.Count).End(xlUp)) - I want to add range ("AR5", sh1.Range("AR" & Rows.Count)
Set f = sh2.Range("O:O").Find(c.Value, , xlValues, xlWhole) - I want to add range ("M:M").Find(c.Value, , xlValues, xlWhole)
I also need to change this part of code. As I update sheets("Wharf Schedules") if no match is found I want cell on sheets("Data") to not change. At present if I update sheets("Wharf Schedules") and the Vessel Name and Voyage Number are not found it deletes data from cell.
'If Not f Is Nothing Then'
Sub Import_Vessel_Availablility_Update()
Dim c As Range, f As Range, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Data")
Set sh2 = Sheets("Wharf Schedules")
For Each c In sh1.Range("AT5", sh1.Range("AT" & Rows.Count).End(xlUp))
If c.Value <> "" Then
Set f = sh2.Range("O:O").Find(c.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
sh1.Range("AU" & c.Row) = sh2.Range("Q" & f.Row)
sh1.Range("AV" & c.Row) = sh2.Range("R" & f.Row)
sh1.Range("AW" & c.Row) = sh2.Range("S" & f.Row)
End If
End If
Next
MsgBox "Vessel Availablility details have been updated in main Data Sheet"
End Sub
Thank you in advance
Last edited: