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

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

CodePudding user response：

Take a look at Select Case

Sub mastersheet1()
 
    Dim i As Integer, ws As Worksheet, n As Integer

    Set ws = Sheets("Master")
    With ws
        For i = 13 To 400
            Select Case .Range("O" & i).Value2
                Case "A": n = 1
                Case "B": n = 2
                Case "C": n = 3
                Case "D": n = 4
                Case "E": n = 5
                Case "F": n = 6
                Case "G": n = 7
                Case "H": n = 8
                Case "I": n = 9
                Case Else: n = 0
            End Select
    
            If n > 0 Then
               .Range("P" & n).Copy .Range("P" & i)
               .Range("T" & n & ":CV" & n).Copy .Range("T" & i & ":CV" & i)
            End If
        Next
    End With
       
End Sub

CodePudding user response：

The problem with the copy/paste method is that it is quite slow and inefficient. I would rather use arrays. Here is an example:

Sub mastersheet()
 
Dim i As Integer
Dim arr As Variant 'This is for storing the array
 
Sheets("Master").Select
 
For i = 13 To 400
    If Range("O" & i).Value = "A" Then
        'This is faster than copy/pasting
        Range("P" & i) = Range("P1")
        arr = Range("T1:CV1")
        Range("T" & i & ": CV" & i) = arr
    End If
Next i    
End Sub`