CODEWITHSUNDEEP
excelvba
Neil Artlip
Neil Artlip

Reputation: 11

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 

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

Upvotes: 1

Views: 162

Answers (1)

TinMan
TinMan

Reputation: 7759

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. 

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

Upvotes: 0

Related Questions