Macro Help

[ai1094]

Hello everyone. I am a new user and a beginner in Excel's VBA.

I was wondering how to create a macro that searches for a specific text, and any value associated to that text will shift over to a certain column. I would assume I would need to use an If Then statement, but like I said, I am very fresh to VBA and only know how to record macros as opposed to writing my own.

I would appreciate some help/feedback. Thanks!

 

[JackDanIce]

Helpful hint, people reading posts can't see PC screens. If they can't see your screen, they do not know what the headers are or what columns you want moved

Your explanation is not clear. Why should 333 and 232 move to E2 and F2? Are they found in C2 and D2?

Should 486 move from C3 to E3 and 386 from D3 to F3?

If so, why can't you copy columns C and D to E and F, then use filters to remove the rows you do not want?

Please avoid reply "Because the sheet changes"

Your examples are not precise nor clear so the replies you get back will either mirror or attempt to guess a solution and rarely be exactly what you need.

Save time and be precise at the very beginning, you'll receive better suggestions faster

 

[ai1094]

I have an image that explains EXACTLY what I want to do but I am not able to post a picture from a file source. Only a URL.

 

[JackDanIce]

If you can see picture as well as your pc monitor (that is in front of you, with sheet names, column headers, all the details that were asked previously), I suppose anyone reading should know this information too.

Unfortunately, I'm unable to see your screen or photo.

Similar to earlier replies I've tried to ask specific questions to help provide a suggestion but without specifics I can't do this so maybe someone else reading can help, good luck solving the problem!

 

[ai1094]

Ok so I got it to work but I want to modify it. Here is my code:

Sub test()


Application.ScreenUpdating = False


Dim x As Integer
x = 2


Do Until x = 10000


If Cells(x, 6).Value = "Projected Demand" Then
Cells(x, 11).Cut Cells(x, 14)
Cells(x, 7).Value = "Projected Supply"
Cells(x, 10).Cut Cells(x, 13)
Cells(x, 7).Value = "Projected Supply"
Cells(x, 12).Cut Cells(x, 15)
End If
x = x + 1
Loop


End Sub

Instead of running it 10,000 times. I want to tell the code to start from Row 1 and look until the last column. How would I do that? So kind of like the CTRL+SHIFT+DOWN

 

[JackDanIce]

Try:

Sub Test1()

    Dim x   As Long
    Dim i   As Long
    Dim LR  As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LR = .Cells(.Rows.Count, 6).End(xlUp).Row
        For x = 2 To LR
            If .Cells(x, 6).Value = "Projected Demand" Then
                .Cells(x, 7).Value = "Projected Supply"
                For i = 10 To 12
                    .Cells(x, i).Cut .Cells(x, i + 3)
                Next i
            End If
        Next x
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[ai1094]

Worked like a charm. Thanks!

 

[JackDanIce]

You're welcome, glad it's resolved.

 

[ai1094]

Is there a way to create a macro for a Pivot table that hides certain columns that don't contain any values?