copying vaules from and adjacent cell based on another

[PaulD21]

Hi all,
I am new to VBA so please excuse any or silly errors.

I am trying to step thru the cells in a selected range in a column.

I want to read the value of the first cell Rng(r) and if it contains the text string "total" then copy the value of another cell, 1 up and 1 to the right of that cell, to the cell to the right of the cell Rng(r).
If the text string does not contain "total" then do nothing and evaluate the next in the range using r=r+1.

here is the code, I am strugling to get the if statement to work, and the syntax.

Sub copyDescription()
'Display a messagebox based on the response
If MsgBox("Make sure the range is selected and the cells have valid values. Do you wish to continue? ", vbYesNo) = vbYes Then
'Sub routine for copying descriptions
Dim Rng As Range
Dim maxRows, r As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
r = 1
Do While r <= maxRows
if (IsNumber(Search("total", Rng(r)),ActiveCell.offset(0,1)= ActiveCell.offset(-1,1),""))
On Error Resume Next
End If
r = r + 1
Loop
MsgBox "values entered"
Else
MsgBox "Process aborted" 'Process stopped message
End If

End Sub

[Sintek]

Hi Paul...
Welcome to forum...See Top Yellow Banner regarding sample file with before|after expected output

[Marc L]

According to the attachment an Excel basics VBA demonstration as a beginner starter :

Sub Demo1()
        Dim Rc As Range
    With Sheet2.Range("D10", Sheet2.[C8].End(xlDown)(0, 2))
        If Application.CountBlank(.Cells) Then For Each Rc In .SpecialCells(4): Rc = Rc(0): Next
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Marc L]

According to the attachment the direct beginner formula VBA variation :

Sub Demo1v()
    With Sheet2.Range("D10", Sheet2.[C8].End(xlDown)(0, 2))
        .Value = .Parent.Evaluate("=IF(" & .Address & "=0," & .Offset(-1).Address & "," & .Address & ")")
    End With
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[PaulD21]

Thanks,
These work, now I need to inspect the code to understand why. But I guess that's part of the learning journey.

[PaulD21]

I am trying to see what additions would be to the code if I wanted to copy the value for the rest of the cells in the row,
Specially the Revision, Material, Thickness, Finish, Process, and Vendor columns.
I'm still not totally clear on the syntax used here.