VBA to Find and Replace into different column, same row, SKIP IF Cell has a value

enter image description hereI've developed an elaborate database to log nameplate values of Heater Equipment as it is replaced. For failure trending purposes, I don't want to overwrite the information about the previous installation when a heater is replaced, but rather log the nameplate information into the next series of columns over. I'm looking for code to say; If any column in the "Installation 1" section is filled in, skip to "Installation 2" section and paste...etc. This is button driven in a previous statement. enter code here

enter code here

Sub DataEntry_HeaterInstallations()


Dim cell As Range, rngFind As Range, counter As Long

'Use heater name in cell L3 as the search criteria

With Sheet1
Set rngFind = .Range("L3")
End With

'Search in worksheet "PIPES DATABASE" for heater name

For Each cell In rngFind

Set Found = Sheets("PIPES DATABASE").Range("U1:U1773").Find(What:=cell.Value, _
LookIn:=xlValues, _
MatchCase:=False)

'When a match is found, replace
'Overwrites formulas

If Not Found Is Nothing Then

' INSTALLATION 1

'If Found.Offset(Cells(0, 24), Cells(0, 35)) = blank Then

Found.Offset(0, 24).Value = cell.Offset(2, -7).Value 'Date
Found.Offset(0, 26).Value = cell.Offset(27, -7).Value 'Heater Length - Hot
Found.Offset(0, 27).Value = cell.Offset(28, -7).Value 'Heater Length - Cold
Found.Offset(0, 28).Value = cell.Offset(26, 4).Value 'Heater Ohms (Per/Ft)
Found.Offset(0, 29).Value = cell.Offset(27, 4).Value 'Heater Ohms Total
Found.Offset(0, 30).Value = cell.Offset(28, 15).Value 'Heater Voltage (VAC)
Found.Offset(0, 31).Value = cell.Offset(26, 15).Value 'Heater Power (Wt/Ft)
Found.Offset(0, 32).Value = cell.Offset(27, 15).Value 'Heater Power TOTAL (Watts)
Found.Offset(0, 33).Value = cell.Offset(26, -7).Value 'Manufacturer
Found.Offset(0, 34).Value = cell.Offset(3, -7).Value 'Work Order #
Found.Offset(0, 35).Value = cell.Offset(5, -7).Value 'Technician Name


' INSTALLATION 2

Found.Offset(0, 38).Value = cell.Offset(2, -7).Value 'Date
Found.Offset(0, 40).Value = cell.Offset(27, -7).Value 'Heater Length - Hot
Found.Offset(0, 41).Value = cell.Offset(28, -7).Value 'Heater Length - Cold
Found.Offset(0, 42).Value = cell.Offset(26, 4).Value 'Heater Ohms (Per/Ft)
Found.Offset(0, 43).Value = cell.Offset(27, 4).Value 'Heater Ohms Total
Found.Offset(0, 44).Value = cell.Offset(28, 15).Value 'Heater Voltage (VAC)
Found.Offset(0, 45).Value = cell.Offset(26, 15).Value 'Heater Power (Wt/Ft)
Found.Offset(0, 46).Value = cell.Offset(27, 15).Value 'Heater Power TOTAL (Watts)
Found.Offset(0, 47).Value = cell.Offset(26, -7).Value 'Manufacturer
Found.Offset(0, 48).Value = cell.Offset(3, -7).Value 'Work Order #
Found.Offset(0, 49).Value = cell.Offset(5, -7).Value 'Technician Name

' INSTALLATION 3

Found.Offset(0, 52).Value = cell.Offset(2, -7).Value 'Date
Found.Offset(0, 54).Value = cell.Offset(27, -7).Value 'Heater Length - Hot
Found.Offset(0, 55).Value = cell.Offset(28, -7).Value 'Heater Length - Cold
Found.Offset(0, 56).Value = cell.Offset(26, 4).Value 'Heater Ohms (Per/Ft)
Found.Offset(0, 57).Value = cell.Offset(27, 4).Value 'Heater Ohms Total
Found.Offset(0, 58).Value = cell.Offset(28, 15).Value 'Heater Voltage (VAC)
Found.Offset(0, 59).Value = cell.Offset(26, 15).Value 'Heater Power (Wt/Ft)
Found.Offset(0, 60).Value = cell.Offset(27, 15).Value 'Heater Power TOTAL (Watts)
Found.Offset(0, 61).Value = cell.Offset(26, -7).Value 'Manufacturer
Found.Offset(0, 62).Value = cell.Offset(3, -7).Value 'Work Order #
Found.Offset(0, 63).Value = cell.Offset(5, -7).Value 'Technician Name
End If

Next cell

MsgBox "Database Updated"


End Sub


Sheet1

Sheets("PIPES DATABASE")

Sheets("PIPES DATABASE")

1 Answer
1

Notice in my refactored code that I modified Range("U1:U1773") to extend from U1 to the last used row. I also added a loop to find the next installation.

Range("U1:U1773")

U1

Sub DataEntry_HeaterInstallations()

Dim cell As Range, rngFind As Range, counter As Long
'Use heater name in cell L3 as the search criteria

With Sheet1
Set rngFind = .Range("L3")
End With

'Search in worksheet "PIPES DATABASE" for heater name

For Each cell In rngFind
With Sheets("PIPES DATABASE")
Set Found = .Range("U1", .Range("U" & .Rows.Count).End(xlUp)).Find(What:=cell.Value, LookIn:=xlValues, MatchCase:=False)
End With

'When a match is found, replace
'Overwrites formulas

If Not Found Is Nothing Then

' INSTALLATION 1

Do Until Found.Offset(0, 24).Value = vbNullString
Set Found = Found.Offset(0, 14)
Loop

Found.Offset(0, 24).Value = cell.Offset(2, -7).Value 'Date
Found.Offset(0, 26).Value = cell.Offset(27, -7).Value 'Heater Length - Hot
Found.Offset(0, 27).Value = cell.Offset(28, -7).Value 'Heater Length - Cold
Found.Offset(0, 28).Value = cell.Offset(26, 4).Value 'Heater Ohms (Per/Ft)
Found.Offset(0, 29).Value = cell.Offset(27, 4).Value 'Heater Ohms Total
Found.Offset(0, 30).Value = cell.Offset(28, 15).Value 'Heater Voltage (VAC)
Found.Offset(0, 31).Value = cell.Offset(26, 15).Value 'Heater Power (Wt/Ft)
Found.Offset(0, 32).Value = cell.Offset(27, 15).Value 'Heater Power TOTAL (Watts)
Found.Offset(0, 33).Value = cell.Offset(26, -7).Value 'Manufacturer
Found.Offset(0, 34).Value = cell.Offset(3, -7).Value 'Work Order #
Found.Offset(0, 35).Value = cell.Offset(5, -7).Value 'Technician Name
End If

Next cell

MsgBox "Database Updated"


End Sub


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.