I'm pulling my hair out!
I have 2 sheets.
Box & Data
Box is very simple - put in a Nominal Code, and an amount (account to be credited), and another nominal code and an amount (account to be debited).
Data is a list of all Nominal codes - with 2 columns next to it, Credit & Debit.
When the macro is run, it:
The problem:
When BOTH of the nominal codes used as new (ie. the macro is to add them to the list, it comes up with an error)
When it's the FromNominal, it's fine.
When it's the ToNominal, it's fine.
It's only when both don't already exist.
Anyone see the problem with the below macro?
Excuse the novice macro writing!!
The error
Runtime error '91':
Object variable or With block variable not set.
(the red text is the selected error text)
I have 2 sheets.
Box & Data
Box is very simple - put in a Nominal Code, and an amount (account to be credited), and another nominal code and an amount (account to be debited).
Data is a list of all Nominal codes - with 2 columns next to it, Credit & Debit.
When the macro is run, it:
- Dim's all the values - such as FromNominal, ToNominal, FromAmount etc
- Check they are the same
- Checks whether the FromNominal already exists in the list of nominal codes
- If so, it then copies the amounts from Box to Data
- If not, tells you it's going to add it, and then add's it to the bottom of the list
- It then proceeds to copy the values as per normal
- And finally confirms it has copied the data correctly.
The problem:
When BOTH of the nominal codes used as new (ie. the macro is to add them to the list, it comes up with an error)
When it's the FromNominal, it's fine.
When it's the ToNominal, it's fine.
It's only when both don't already exist.
Anyone see the problem with the below macro?
Excuse the novice macro writing!!
The error
Runtime error '91':
Object variable or With block variable not set.
(the red text is the selected error text)
Code:
Sub Temp()
Beginning:
Dim FromNominal As Variant
Dim ToNominal As Variant
Dim FromAmount As Variant
Dim ToAmount As Variant
Dim YesNo As Variant
Dim SubtotalFrom As Variant
Dim SubtotalTo As Variant
Sheets("Box").Select
If ToAmount < FromAmount Or ToAmount > FromAmount Then
MsgBox "Amounts do not equal. Please recheck."
GoTo TheEnd
End If
FromNominal = Range("E5").Value
ToNominal = Range("E7").Value
FromAmount = Range("G5").Value
ToAmount = Range("G7").Value
If ToAmount < FromAmount Or ToAmount > FromAmount Then
MsgBox "Amounts do not equal. Please recheck."
GoTo TheEnd
End If
Sheets("Data").Select
SubtotalFrom = Range("C300").Value
SubtotalTo = Range("D300").Value
On Error GoTo AddFrom
Cells.Find(What:=FromNominal, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Value + FromAmount
On Error GoTo AddTo
[COLOR="Red"] Cells.Find(What:=ToNominal, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate[/COLOR]
On Error GoTo 0
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = ActiveCell.Value + ToAmount
If Range("C300").Value = SubtotalFrom + FromAmount Then
GoTo NearlyAllGood
Else
MsgBox "The value from " & FromNominal & "was not entered succesfully. Please examine", vbOKOnly
End If
NearlyAllGood:
If Range("D300").Value = SubtotalTo + ToAmount Then
GoTo AllGood
Else
MsgBox "The value from " & ToNominal & "was not entered succesfully. Please examine", vbOKOnly
GoTo TheEnd
End If
AllGood:
Sheets("Box").Select
MsgBox "Credit " & FromNominal & ": £" & FromAmount & vbCrLf & "Debit " & ToNominal & ": £" & ToAmount & vbCrLf & "have been entered succesfully", vbOKOnly
End
AddFrom:
YesNo = MsgBox("Nominal code " & FromNominal & " will be added", vbYesNo)
Select Case YesNo
Case vbYes
For I = 1 To 65536
If ActiveCell.Value = Empty Then
BCell = "B" & CStr(I - 1)
NBCell = "B" & CStr(I - 2)
Else
Range("B" & CStr(I + 1)).Select
End If
Next I
ActiveCell.Value = FromNominal
GoTo Beginning
Case vbNo
MsgBox "You said No"
Sheets("Box").Select
End Select
GoTo TheEnd
AddTo:
YesNo = MsgBox("Nominal code " & ToNominal & " will be added", vbYesNo)
Select Case YesNo
Case vbYes
For I = 1 To 65536
If ActiveCell.Value = Empty Then
BCell = "B" & CStr(I - 1)
NBCell = "B" & CStr(I - 2)
Else
Range("B" & CStr(I + 1)).Select
End If
Next I
ActiveCell.Value = ToNominal
GoTo Beginning
Case vbNo
MsgBox "You said No"
Sheets("Box").Select
End Select
GoTo TheEnd
TheEnd:
Sheets("Box").Select
End Sub![A[L]C](/styles/overclockers/avatars/7.png){kind=avatar}
