Help on VBA request (next step)

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
Hello,

Following this topic : https://www.experts-exchange.com/questions/29166890/Help-on-VBA-request.html

My request was perfectly answered by both of the proposals

I've chosen to use the second one as it's easier for me to understand, only problem is that sometimes I will have to use the macro when there are already numbers in column H

My fault as I first said column H was empty when it actually wont always be empty

Could you please help me change the macro so that it will not touch column H if there are already numbers within ?

Thank you very much for your help & kind regards,

Mélanie
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You may tweak that like this...
Sub Findandcut()
Dim r       As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, "F").End(xlUp).row

For r = 1 To LastRow
    'Check if "C" appears in the column F
    If Range("F" & row).Value = "C" Then
        'Copy the respective cell from G to H and then blank the source cell at G.
        If Range("H" & r).Value = "" Then
            Range("H" & r).Value = Range("G" & r).Value
            Range("G" & r).Value = ""
        End If
    End If
Next

End Sub

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
Hello Neeraj,

I remember your kind help from the last time, glad to see you again ;)

Thanks for your proposal, I can't run the macro : got a :

Run-time error '1004':
Method 'Range' of object'_Global' failed

The debug tool yellows the line : "If Range ("F" & row).Value = "C" Then
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Hi Magpie,

My bad, I forgot to replace the row variable with r in that line.
Please replace the line causing the issue with the following one...

If Range("F" & r).Value = "C" Then

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
Perfect !! Thank you very much Neeraj :)
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Mélanie! :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial