VNouBA
05-16-2012, 10:34 AM
I have the following VB Code:
If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
For i = 1 To Target ' stops the code looping
tmpArr = tmpArr & "," & i
Next i
With Target.Offset(, 1).Resize(Target)
.NumberFormat = "000"
.Value = Application.Transpose(Split(Mid(tmpArr, 2), ","))
For i = 1 To Target
Target.Offset(i - 1, 1).Value = Target.Offset(0, -1).Value & "/" & Format(Target.Offset(i - 1, 1).Value, "000/HS")
Next i
End With
End If
End If
The function is: If a number is added to Column B then “x” amount of rows is inserted with the information from Column A plus “/” and “000/HS” in Column C.
I need to add the following:
If a number is added in Column B then add “x” amount of rows with the information from Column A plus “/” and “000/HS” in Column C Then Search Column A from that same row to add the information from Column A to the extra rows but downwards from Column A.
Example if you take row 7
If Column A Row 7 text is “Report” Then the user adds “004” to Column B Row 7, the code will add three rows in column C adding the following information.
C7 = Report/001/HS; C8 = Report/002/HS; C9 = Report/003/HS; C10 = Report/004/HS.
This is the existent code.
I need the code to also Add the “Report” from Column A Row 7 to Column A Row 8,9 and 10.
Please help
If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
For i = 1 To Target ' stops the code looping
tmpArr = tmpArr & "," & i
Next i
With Target.Offset(, 1).Resize(Target)
.NumberFormat = "000"
.Value = Application.Transpose(Split(Mid(tmpArr, 2), ","))
For i = 1 To Target
Target.Offset(i - 1, 1).Value = Target.Offset(0, -1).Value & "/" & Format(Target.Offset(i - 1, 1).Value, "000/HS")
Next i
End With
End If
End If
The function is: If a number is added to Column B then “x” amount of rows is inserted with the information from Column A plus “/” and “000/HS” in Column C.
I need to add the following:
If a number is added in Column B then add “x” amount of rows with the information from Column A plus “/” and “000/HS” in Column C Then Search Column A from that same row to add the information from Column A to the extra rows but downwards from Column A.
Example if you take row 7
If Column A Row 7 text is “Report” Then the user adds “004” to Column B Row 7, the code will add three rows in column C adding the following information.
C7 = Report/001/HS; C8 = Report/002/HS; C9 = Report/003/HS; C10 = Report/004/HS.
This is the existent code.
I need the code to also Add the “Report” from Column A Row 7 to Column A Row 8,9 and 10.
Please help