vendredi 24 septembre 2021

Selecting Multiple Columns with and a Specific Row with For Loop VBA

I'm trying to select multiple columns for i rows depending on a For loop. The idea is to check whether a specific cell meets the criteria. If so, copy the formulas associated with that specific segment to the same row as that observation.

i.e:

for i = 13

If O(i) = segment A, copy and paste formula from $P$1 to P(i)

AND

Copy and paste formulas in T1:CV1 to T(i) : CV (i)

(Please keep in mind there are hidden columns between T and CV, I assume these won't have anything to do with the outcome since they are hidden but wanted to note regardless.)

So far, I've tried using the code : Range("T" & i : "CV" & i).Select . I know this is wrong but just wanted to give an idea. The full code is attached below. Any help is appreciated!

Sub mastersheet()
 
Dim i As Integer
 
Sheets("Master").Select
 
For i = 13 To 400
    If Range("O" & i).Value = "A" Then
        Range("P1").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T1:CV1").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "B" Then
        Range("P2").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T2:CV2").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "C" Then
        Range("P3").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T3:CV3").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "D" Then
        Range("P4").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T4:CV4").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "E" Then
        Range("P5").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T5:CV5").Select
        Selection.Copy
       Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "F" Then
        Range("P6").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T6:CV6").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "G" Then
        Range("P7").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T7:CV7").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "H" Then
        Range("P8").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T8:CV8").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    ElseIf Range("O" & i).Value = "I" Then
        Range("P9").Select
        Selection.Copy
        Range("P" & i).Select
        ActiveSheet.Paste
        Range("T9:CV9").Select
        Selection.Copy
        Range("T" & i : "CV" & i).Select
        ActiveSheet.Paste
    End If
Next i
       
    
End Sub

Aucun commentaire:

Enregistrer un commentaire