Conclude new account number

[abu abdullah........halsaeed85]

I Have 4 Columns Table To Define Accounts Info
Col. A Account Name
Col. B Account Code ( Number )
Col. C Account Type ( If Main Or Nominal Account )
Col. D Account Parent
I Use Form To Add New Accounts Info With Combobox To Define/Chose
Account Parent & Other 3 Textbox For Same Other Account Info , What
I Need Is To Have ( Max Account Number + 1 ) From Account Group
Numbers In Account Number Textbox As Suggested New Account Number
When I Define In Combobox1 Account Parent , So What Suggested Userform
Code To Manage This Task .
As Example Suppose That I Add New Expenses Account To Operating
Expenses As Main Or Parent Account To These Accounts Category Such As
General Maintenance Expenses And The Last Account Number Added To That
Category Is 41210008 So I Have To Get In Account Number Textbox
41210009 As Suggested New Account Number .
Any Help Advance Appreciated .

 

[joel]

As you highlighted in your request a text box is TEXT so you have t
perticulir in what insturction to use in getting the max value an
finding account numbers on the worksheet. I usually get a range objec
for the location of the Account number like this

with Sheets"(sheet1")
LastRow = .Range("A" & rows.count).end(xlup).row
'note I assume ther is a header row
Set AccountRange = .Range("A1:A" & LastRow)


TextValue = me.combobox1.Text
NumValue = Val(texttValue)

'Get Max value of Accont numbers from worksheet
MaxVal = worksheetfunction.Max(AccountRange)


'Use this code to find an account number
'note find looks for the text value
set c = AccountRange.find(what:=textValue, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Did not find Account : " & textVal)
else
msgbox("Found Account : " & textVal & " at cell : " & c.address

end if
end wit

 

[abu abdullah........halsaeed85]

As you highlighted in your request a text box is TEXT so you have to
perticulir in what insturction to use in getting the max value and
finding account numbers on the worksheet.   I usually get a range object
for the location of the Account number like this

with Sheets"(sheet1")
LastRow  = .Range("A" & rows.count).end(xlup).row
'note I assume ther is a header row
Set AccountRange = .Range("A1:A" & LastRow)

TextValue = me.combobox1.Text
NumValue = Val(texttValue)

'Get Max value of Accont numbers from worksheet
MaxVal = worksheetfunction.Max(AccountRange)

'Use this code to find an account number
'note find looks for the text value
set c = AccountRange.find(what:=textValue, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Did not find Account : " & textVal)
else
msgbox("Found Account : " & textVal & " at cell : " & c.address)

end if
end with

--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=175168

Microsoft Office Help

Dear Joel
First Thanks For Your Quick Replay But I Am So Sorry Because Still
Not Helpful Maybe Because I Couldn’t Explain Clearly So I’ll Try To
Explain Again :
1- I Need To Add Account Info Consists Of 4 Info Through Userform
2- Userform Consists Of 4 Fields : Textbox1 To Put Account Name –
Textbox2 To Put Account Number – Textbox3 To Put Account Type ( Main
Or Nominal Account ) – Combobox1 Related To Col. D In Data Table To
Chose From Combobox1 Account Parent ( Account Main Category Related
To ) .
3- I Need If I Chose Parent As Example “ Fixed Assets “ From Combobox1
I Got In Textbox2 ((Max Account Number Existed Related To That Parent
Account “From Account Numbers Col. B Data Table “) + 1) So If Max
Account Number Related To That Parent “ Fixed Assets “ Is 11011007 So
I Must See In Textbox2 110011008 As Next Available Number To Give It
To My New Account I Want To Add It .
4- I Remind That Data Table Source/Entry Data Designed As I Mentioned
In My Original Post .
Hop I Could Clarify My Problem .
Any Help Advance Appreciated .