Help on VBA request

Magpie Bavarde
Magpie Bavarde used Ask the Experts™
on
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,

Mélanie
macro-should-do-this.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
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

Author

Commented:
Hello Norrie,

That's perfect and works like a charm !

Thank you very much for your kind help :)

Best regards,

Mélanie
NoahHardware Tester and Debugger
Commented:
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
    Next

End Sub

Open in new window

Magpie BavardeExecutive Assistant

Author

Commented:
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

Mélanie
NorieAnalyst Assistant

Commented:
Mélanie

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

Author

Commented:
Hello,

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,

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