View Full Version : Vba code for parent-child
ken4ward
09-06-2012, 02:42 AM
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
09-06-2012, 04:05 AM
Just add this formula to B1 and copy down
=COUNTIF($A$1:A1,A1)
ken4ward
09-06-2012, 04:25 AM
Thanks. I ma doing this from coding not on the excel sheet. What is the VBA code pls. help
ken4ward
09-06-2012, 04:26 AM
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.
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
09-06-2012, 04:40 AM
With Activesheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B1").Resize(lastrow).Formula = "=COUNTIF($A$1:A1,A1)"
End With
ken4ward
09-06-2012, 05:04 AM
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.
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
09-06-2012, 08:05 AM
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
09-06-2012, 08:12 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.