I copied most of the code below from somewhere and modified it a little and it basically works how i wanted except for some conditions that I wanted to add
The original code with slight amendment and which works is below:
I wanted to add the following:
1) To paste the first value 1 row below what the macro currently does.
2) Then I wanted to add more conditions, ie, if the data in column 2 in the array starts with "SV-VAS" or "LIC-", then should only paste ONCE. (right now, it's not pasting anything at all for this condition (the other conditions are ok)).
Thanks in advance!
The original code with slight amendment and which works is below:
Code:
Option ExplicitSub INDO()
Dim arr As Variant
Dim wsO As Worksheet
Dim this As Integer
Dim i, h As Integer
arr = ThisWorkbook.ActiveSheet.UsedRange
Set wsO = ThisWorkbook.ActiveSheet
For i = LBound(arr, 1) To UBound(arr, 1)
If IsNumeric(arr(i, 4)) Then
this = arr(i, 4)
For h = 1 To this
Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Value = arr(i, 2)
Next h
End If
Next i
End Sub
I wanted to add the following:
1) To paste the first value 1 row below what the macro currently does.
2) Then I wanted to add more conditions, ie, if the data in column 2 in the array starts with "SV-VAS" or "LIC-", then should only paste ONCE. (right now, it's not pasting anything at all for this condition (the other conditions are ok)).
Code:
Sub INDO()
Dim arr As Variant
Dim wsO As Worksheet
Dim this As Integer
Dim i, h As Integer
arr = ThisWorkbook.ActiveSheet.UsedRange
Set wsO = ThisWorkbook.ActiveSheet
For i = LBound(arr, 1) To UBound(arr, 1)
If IsNumeric(arr(i, 4)) Then
this = arr(i, 4)
For h = 1 To this
If Left(arr(i, 2), 4) = "LIC-" Or Left(arr(i, 2), 6) = "SV-VAS" Then
[COLOR=#ff0000] Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Value = arr(1, 2)[/COLOR]
ElseIf Left(arr(i, 2), 6) = "GL-VAS" Then
'Do nothing
Else
Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Value = arr(i, 2)
End If
Next h
End If
Next i
End Sub
Thanks in advance!