Creating a macro to update cells

[kevin67]

Hello Excel expert or VB expert
I have a sheet one about 22000 records as per below. In column 1 Header is LVL and Column 2 header GROUPNAME. if condition is met that is if A2 = 0 then B2 will be copied to D2 if A2 =1 then B2 will be copied to E2 , if A2=3 then B2 will be copied to F2. the values in the column LVL can be between 0 to 10. I am looking like an array to do this task . Any help will be grateful

Thanks

LVL	GROUPNAME
0	AB
1	BC
0	DD
1	AX
2	AZ
3	NO

Here is the code i am using but getting error Runtime error 1004 Method Range of Object worksheet failed

Sub Replace_responseRegion()


Dim lr As Long
Dim x As Integer
Dim ary As Variant, i As Long
ary = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

'Declare the workbook to retrieve info'
Dim wkRead As Workbook
Set wkRead = Workbooks("Masterfile.xlsx")

'Declare the worksheet from the workbook maysurvey.xlsx'
Dim shtRead As Worksheet
Set shtRead = wkRead.Worksheets("Master")

lr = shtRead.Range("A" & Rows.Count).End(xlUp).row
Debug.Print lr


With shtRead
For i = LBound(ary) To UBound(ary)


shtRead.Range("A2:B" & lr).AutoFilter 1, ary(i)

If i = 0 Then

shtRead.Range("B2:B" & lr).Copy shtRead.Range("D2" & Rows.Count).End(3)(1)
ElseIf i = 1 Then
shtRead.Range("D3:D" & lr).Copy shtRead.Range("B2" & Rows.Count).End(3)(1)
ElseIf i = 2 Then
shtRead.Range("D4:D" & lr).Copy shtRead.Range("B2" & Rows.Count).End(3)(1)
End If

Next
End With


End Sub

 

[Peter_SSs]

if A2 = 0 then B2 will be copied to D2 if A2 =1 then B2 will be copied to E2 , if A2=3 then B2 will be copied to F2.

Did you mean if A2=2?

If so, try this with a copy of your data. It is written for the active sheet as I don't have your workbook set-up. Hopefully you can adapt if it is what you want.

Sub AllocateToColumn()
  Dim a As Variant, b As Variant
  Dim i As Long
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value2
  ReDim b(1 To UBound(a), 1 To 11)
  For i = 1 To UBound(a)
    b(i, a(i, 1) + 1) = a(i, 2)
  Next i
  Range("D2").Resize(UBound(b), UBound(b, 2)).Value = b
End Sub

 

[kevin67]

Did you mean if A2=2?

If so, try this with a copy of your data. It is written for the active sheet as I don't have your workbook set-up. Hopefully you can adapt if it is what you want.

Sub AllocateToColumn()
  Dim a As Variant, b As Variant
  Dim i As Long
 
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value2
  ReDim b(1 To UBound(a), 1 To 11)
  For i = 1 To UBound(a)
    b(i, a(i, 1) + 1) = a(i, 2)
  Next i
  Range("D2").Resize(UBound(b), UBound(b, 2)).Value = b
End Sub

Thanks a lot Peter, it works as per requested

 

[smozgur]

Thanks a lot Peter, it works as per requested

And that's the reason why I switched the marked solution post. We should mark the best answer that solved our question to help future readers.

 

[Peter_SSs]

Thanks a lot Peter, it works as per requested

You are welcome. Thanks for the follow-up.