Difficult macro for a VBA novice (info in 1st post got messed up)

Posted by Felicia P. on July 15, 2000 12:04 PM

I have to key in every month hours spend for various accounts. The spreadsheet is setup in Jan for all 12 months and has zero already in all cells.
If I have a new account in a month, I have to insert a new row and enter the account name (can have any number of letters) and #(3 digits).
Accounts are not in alfabetical or numerical order.
To simplify this example I'll use only the first 3 months.
In this example Jan and Feb are already done; the reporting month is March.

A1 Accounts
A2 ABCDE (012)
A3 EFG (021)
A4 HIJL (055)
A5 MNOPQRS (034)

B1 Jan
B2 45.00
B3 0.00
B4 2.00
B5 0.00

C1 Feb
C2 0.00
C3 3.00
C4 7.00
C5 22.00

D1 Mar
D2 0.00
D3 4.00
D4 12.00
D5 31.00

Reporting month
E1 Accounts
E2 055
E3 021
E4 034

F1 Mar
F2 12.00
F3 4.00
F4 31.00

I want to set up columns E and F as the Current Month Reporting area where I'll enter in E2 and down the accounts for which I have hours in March and
in F2 and down I'll enter number of hours for the accounts listed in E2 and down.
After I enter the current month data, a macro should do the following:
look after the first account in column E (055) find it in column A and copy the corresponding hours from column F in the current month column (D1).
In case that an account in column E is new (can not be found in column A) a message box should say "You have to insert a new row for new account." and the macro to stop until the new row is inserted and resume to run after that.
I am new at macros and perhaps it can not do all these but any suggestions will be greatly appreciated.
Thank you in advance.

Posted by Felicia on July 26, 0100 6:30 AM

Help, Compile Error

Thanks Ryan for your time. I was away so only last night I adapted your code to my project and when debugging I get a compile error message referring to GetRow at this point in code:

EnterRow=GetRow

The error says: "Wrong number of arguments or invalide property assignment.
I also have a question; what is EnterRow?
Also I do not know if it counts but the name of the months are in the date format (MMM); in B1 JAN I have this formula =DATE(YEAR($C$8),1,1), in C1 for FEB I have =DATE(YEAR($C$8),2,1), and in $C$8 I enter every month, the reporting month as date 06/01/2000 and is in (MMMM) format.
Thank you again for your time.

Sub EnterinHours() Dim LastRow As Long Dim MonthRow As String MonthRow = GetMonthRow(Cells(1, 15).Value) On Error Resume Next Application.ScreenUpdating = False EnterRow = GetRow(Cells(x, 14).Text) If EnterRow = "" Then Call EnterNewAcct(Cells(x, 14).Text) EnterRow = GetRow(Cells(x, 14).Text) End If Cells(EnterRow, MonthRow).Value = Cells(x, 15).Value Next x Function GetRow(Acct) Dim Cell As Range For Each Cell In Range("A2:A" & Range("A65000").End(xlUp).Row) If Cell.Text = "" Then Exit For If Mid(Cell.Value, InStr(1, Cell.Value, "(") + 1, _ InStr(InStr(1, Cell.Value, "(") + 1, Cell.Value, ")") _ - InStr(1, Cell.Value, "(") - 1) = Acct Then GetRow = Cell.Row Exit For End If Next Cell End Function Function GetMonthRow(Month) Dim x As Integer If Cells(1, x).Value = Month Then GetMonthRow = x Exit For End If Next x End Function Sub EnterNewAcct(Acct) Dim AcctName As String AcctName = InputBox("Please enter a name for account " & Acct, "Enter Account") If AcctName = "" Then Exit Sub Cells(Range("A65000").End(xlUp).Row + 1, 1).Value = AcctName & " (" & Acct & ")" For x = 2 To 13 Cells(Range("A65000").End(xlUp).Row, x).Value = 0 Next x

Posted by Ryan on July 15, 0100 1:36 PM

Felicia,

Is all this information on one sheet. For example, column A contains all the accounts, column B - M contain the months, column N contains the acct numbers to be inputed and column O contains the hours to be inputed? I wrote code to do what you need, but need to make sure where all your info is.

Ryan

Posted by Felicia on July 15, 0100 2:25 PM

Yes Ryan all are on one sheet thank you so much

Yes all are on one sheet.

Posted by Ryan on July 15, 0100 3:35 PM

Re: Yes Ryan all are on one sheet thank you so much

Felicia,
This code assumes everything I asked you before. Once you have the acct numbers entered in and the hours entered in, run this macro and all will be done. If there is a new account number, an Input Box will pop up asking you for the name of the new account. There is no need to put in the 3 numbers, for the program will put them in, just enter in the name. If there is something else let me know. Let me know how it works!

Ryan

Sub EnterinHours()
Dim LastRow As Long
Dim MonthRow As String

LastRow = Range("N65000").End(xlUp).Row
MonthRow = GetMonthRow(Cells(1, 15).Value)
On Error Resume Next
Application.ScreenUpdating = False

For x = 2 To LastRow
EnterRow = GetRow(Cells(x, 14).Text)
If EnterRow = "" Then
Call EnterNewAcct(Cells(x, 14).Text)
EnterRow = GetRow(Cells(x, 14).Text)
End If
Cells(EnterRow, MonthRow).Value = Cells(x, 15).Value
Next x

Application.ScreenUpdating = True

End Sub
Function GetRow(Acct)
Dim Cell As Range

For Each Cell In Range("A2:A" & Range("A65000").End(xlUp).Row)
If Cell.Text = "" Then Exit For
If Mid(Cell.Value, InStr(1, Cell.Value, "(") + 1, _
InStr(InStr(1, Cell.Value, "(") + 1, Cell.Value, ")") _
- InStr(1, Cell.Value, "(") - 1) = Acct Then
GetRow = Cell.Row
Exit For
End If
Next Cell
End Function
Function GetMonthRow(Month)
Dim x As Integer

For x = 2 To 13
If Cells(1, x).Value = Month Then
GetMonthRow = x
Exit For
End If
Next x

End Function
Sub EnterNewAcct(Acct)
Dim AcctName As String

AcctName = InputBox("Please enter a name for account " & Acct, "Enter Account")
If AcctName = "" Then Exit Sub
Cells(Range("A65000").End(xlUp).Row + 1, 1).Value = AcctName & " (" & Acct & ")"
For x = 2 To 13
Cells(Range("A65000").End(xlUp).Row, x).Value = 0
Next x

End Sub