Help on VBA request

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

Im working on my Travel expanses workflow and was able to get pretty much all I needed done with VBA, except this part where your help will be very welcome, pretty please :)

Suppose columns F, G, H

Column F is full of "C" or "D"
Column G is full of numbers
Column H is empty

I want that IF it's a "C" in F, then the number in G is moved from G to H, for all the column (no more than 500 rows max, different number of rows each time)

Thank you very much for your help !

Kind regards,

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Try this.
Dim arrData As Variant
Dim idx As Long

    With Sheets("Sheet7") ' change sheet name as required
        arrData = .Range("F2", .Range("F" & Rows.Count).End(xlUp)).Resize(, 3).Value
        For idx = LBound(arrData, 1) To UBound(arrData, 1)
            If arrData(idx, 1) = "C" Then
                arrData(idx, 3) = arrData(idx, 2)
                arrData(idx, 2) = ""
            End If
        Next idx
        .Range("F2", .Range("F" & Rows.Count).End(xlUp)).Resize(, 3).Value = arrData
    End With

Open in new window

Magpie BavardeExecutive Assistant


Hello Norrie,

That's perfect and works like a charm !

Thank you very much for your kind help :)

Best regards,

NoahHardware Tester and Debugger
Hi there!

A bit late.. But do take a look. You can try this too!

Sub Findandcut()
    Dim row As Long

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

End Sub

Open in new window

Magpie BavardeExecutive Assistant


Hello Noah and thank you very much,

Works like a charm too - and I can actually understand this one ^^ (but I will try to understand the previous one too as there is no better way to learn and progress)

Only thing that bothers me is that in both case, the macro will erase any number already present in column H

But I will launch another topic for that

Thank you very much & Kind regards

NorieAnalyst Assistant


What should happen if there is an existing value in column H?
Magpie BavardeExecutive Assistant



I've launched another topic as I understand you're only "rewarded" once per topic

Is that the right thing to do or should I have continued the discussion here for my second request ?

Thanks & Kind regards,


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