Excel VBA Q

  • Thread starter Thread starter dipsy
  • Start date Start date
D

dipsy

I have 2 columns in a file -
1. Project numbers
2. Plan id

If there is no project no. then I want to take the plan id
and assign it to the project no. I have done this with
formula:
IF(C12="",A12&1) - The last number "1" - I want to
increment it for all the lines for a particular group that
do not have project nos.

So for Group 1 that has 10 items in Plan and of them 6
have Project ids - I would like to have the Planids but
with 1 at the end and then 2 at the end and so forth till
4 at end. SBUBBD1, SBUBBD2, SBUBBD3, SBUBBD4

I would like to do it through VBA.

TIA.
 
I am not too clear with what you require because in your example,
assuming Project in column A and PlanId in column B as you suggest, you
are adding sequence numbers to Project in your formula, not PlanId.
However, if you copy and run this macro you will get my answer and be
able to amend it as necessary.

The macro assumes the PlanId column is full from row 2 and stops when
it finds a blank cell.

'-----------------------------------------
Sub test()
Dim ToRow As Long
Dim NewRef As String
Dim SeqNo As Integer
'--------------------
ToRow = 2
SeqNo = 1
'--------------------
While ActiveSheet.Cells(ToRow, 2) <> ""
If ActiveSheet.Cells(ToRow, 1).Value = "" Then
If SeqNo = 1 Then
NewRef = ActiveSheet.Cells(ToRow - 1, 1).Value
End If
ActiveSheet.Cells(ToRow, 3).Value = NewRef & SeqNo
SeqNo = SeqNo + 1
Else
ActiveSheet.Cells(ToRow, 3).Value = _
ActiveSheet.Cells(ToRow, 1).Value
SeqNo = 1
End If
ToRow = ToRow + 1
Wend
End Sub
'------------------------------------------
 
Back
Top