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