Vba code for parent-child

[ken4ward]

Please kindly follow this analogy.
I have a spreadsheet that I want to populate with values from my code. The values should be in this way:
Example is this:

cell1 cell2
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
this is just a sample code. as we can see, cell1 is a parent to cell2. as long as the value in cell1 does not change cell2 will continue to increment,
when the value of cell1 changes, cell2 will start counting from 1 again.

I have this code i have been trying out but not giving me the result. Pls. how do I achieve this. Thanks God bless in Jesus name.

[Bob Phillips]

Just add this formula to B1 and copy down


=COUNTIF($A$1:A1,A1)

[ken4ward]

Thanks. I ma doing this from coding not on the excel sheet. What is the VBA code pls. help

[ken4ward]

this is my previous code:
If Trim(lRow > 3) Then
If Trim(sheetTrans.Cells(lRow2, 1).Value = ws.Cells(lRow, 1).Value) Then
With ws
.Cells(lRow, 1).Value = Me.txtTransaction.Value
.Cells(lRow, 2).Value = .Cells(lRow - 1, 2).Value + 1
.Cells(lRow, 3).Value = Me.ComboBoxAccount.Value
.Cells(lRow, 7).Value = Me.txtAmount.Value
Exit Sub
End With
End If
End If

If Trim(lRow > 3) Then
If Trim(sheetTrans.Cells(lRow2, 1).Value <> ws.Cells(lRow, 1).Value) Then
With ws
.Cells(lRow, 1).Value = Me.txtTransaction.Value
.Cells(lRow, 2).Value = 1
.Cells(lRow, 3).Value = Me.ComboBoxAccount.Value
.Cells(lRow, 7).Value = Me.txtAmount.Value
Exit Sub
End With
End If
End If
but it's not giving the right answer.

[GTO]

You could still use the formula XLD supplied and just overwrite the return:
Sub xample()
With Sheet1
With .Range(.Cells(1), .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 1)
.Formula = "=COUNTIF($A$1:A1,A1)"
.Value = .Value
End With
End With
End Sub

[Bob Phillips]

With Activesheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("B1").Resize(lastrow).Formula = "=COUNTIF($A$1:A1,A1)"
End With

[ken4ward]

Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Dim sheetTrns As Worksheet
Dim sheetTrans As Worksheet
Dim lRow2 As Long

Private Sub btnClose_Click()
Unload Me
End Sub

'this is the part that is not working fine
Private Sub btnSave_Click()

Set ws = Worksheets("Ac_Entries")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

lRow2 = sheetTrans.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a part number
If Trim(Me.txtTransaction.Value) = "" Then
'Me.cboPart.SetFocus
MsgBox "Please make a transaction"
Exit Sub
End If

If Trim(lRow < 4) Then
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtTransaction.Value
.Cells(lRow, 2).Value = 1
.Cells(lRow, 3).Value = Me.ComboBoxAccount.Value
.Cells(lRow, 7).Value = Me.txtAmount.Value
End With
End If

If Trim(lRow > 3) Then
If Trim(Me.txtTransaction.Value <> ws.Cells(lRow - 1, 1).Value) Then
With ws
.Cells(lRow, 1).Value = Me.txtTransaction.Value
.Cells(lRow, 2).Value = 1
.Cells(lRow, 3).Value = Me.ComboBoxAccount.Value
.Cells(lRow, 7).Value = Me.txtAmount.Value
Exit Sub
End With
End If
End If

End Sub
'This part of code is working fine as suppose
Private Sub btnTransaction_Click()

Set sheetTrans = Worksheets("Txn_Details")
lRow2 = sheetTrans.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If Trim(lRow2 < 3) Then

'copy the data to the database
With sheetTrans
.Cells(lRow2, 1).Value = 1
.Cells(lRow2, 2).Value = Me.ComboUser.Value
.Cells(lRow2, 3).Value = Format(Date, "Medium Date")
.Cells(lRow2, 4).Value = Format(Time, "Medium Time")
.Cells(lRow2, 5).Value = Me.ComboBoxDesc.Value
.Cells(lRow2, 6).Value = Me.ComboBoxWho.Value
Me.txtTransaction.Value = .Cells(lRow2, 1).Value = 1

End With
End If

If Trim(lRow2 > 2) Then
With sheetTrans
.Cells(lRow2, 1).Value = .Cells(lRow2 - 1, 1).Value + 1
.Cells(lRow2, 2).Value = Me.ComboUser.Value
.Cells(lRow2, 3).Value = Format(Date, "Medium Date")
.Cells(lRow2, 4).Value = Format(Time, "Medium Time")
.Cells(lRow2, 5).Value = Me.ComboBoxDesc.Value
.Cells(lRow2, 6).Value = Me.ComboBoxWho.Value
Me.txtTransaction.Value = .Cells(lRow2 - 1, 1).Value + 1

End With
End If
End Sub

Private Sub UserForm_Initialize()
Dim UserList As Range
Dim DescList As Range
Dim AccountList As Range
Dim sheetTrans As Worksheet
Set sheetTrans = Worksheets("LookupTables")

For Each UserList In sheetTrans.Range("Abiola")
With Me.ComboUser
.AddItem UserList.Value
End With
Next UserList

For Each UserList In sheetTrans.Range("Abiola")
With Me.ComboBoxWho
.AddItem UserList.Value
End With
Next UserList

For Each DescList In sheetTrans.Range("Opening")
With Me.ComboBoxDesc
.AddItem DescList.Value
End With
Next DescList

For Each AccountList In sheetTrans.Range("AccountName")
With Me.ComboBoxAcc
.AddItem AccountList.Value
End With
Next AccountList

For Each AccountList In sheetTrans.Range("AccountName")
With Me.ComboBoxAccount
.AddItem AccountList.Value
End With
Next AccountList

Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtTime.Value = Format(Time, "Medium time")
'Me.txtTransaction.Value = sheetTrans.Cells(lRow, 1).Value

End Sub

As explained earlier, please just keep it simple, maybe, probably help me reframe this code as it is suppose to be. I am a total novice to excel/vba coding. I do not understand your previous post on this.

[snb]

if the column A has been filled

put into Cel B1: 1
in cel B2

=IF(A2=A1;B1+1;1)


and fill the other cells in column B with this formula.

[ken4ward]

I am not doing it from the excel form. I am writing a VBA code for it. Please if in anyway it's possible you help me it is reframing my code bcos I am very new to VBA and do not understand many of it terms

[ken4ward]

I am not doing it from the excel form. I am writing a VBA code for it. Please if in anyway it's possible you help me it is reframing my code bcos I am very new to VBA and do not understand many of it terms