This is my first foray into writing VBA code from scratch and I am making a few mistakes. I am trying to compare a user input string with the contents of a set of cells, each one in turn and pick out values from a cells in the same row if the comparison is true. The code I have written is below:
Sub InvoiceData()
'Declare Variables
Dim i As Integer
Dim LastInvoiceNumber As String
Dim InvoiceCount As Integer
Dim InvoiceTotal As Long
Dim CompanyCode As String
Dim x As Integer
'Prevent Screen flicker and Speed up code
Application.ScreenUpdating = False
'Accept Company Code from user
CompanyCode = Application.InputBox("Enter Company code")
'Start loop for Calculations and positioning
For i = 1 To 12
x = i + 6
If ActiveSheet.cell(x, 9).Value Like CompanyCode & "*" = True Then
LastInvoiceNumber = ActiveSheet.cell(x, 9).Value
InvoiceCount = InvoiceCount + 1
InvoiceTotal = InvoiceTotal + ActiveSheet.cell(x, 13).Value
End If
Next i
'pasting results to spreadsheet
Set ActiveSheet.cell(6, 13).Value = InvoiceTotal
Set ActiveSheet.cell(7, 13).Value = InvoiceCount
Set ActiveSheet.cell(6, 13).Value = LastInvoiceNumber
Application.ScreenUpdating = True
End Sub
The "if statement" seems to be the problem.
Can any one help or give me a better way (even a non-VBA way) of doing this??
Sub InvoiceData()
'Declare Variables
Dim i As Integer
Dim LastInvoiceNumber As String
Dim InvoiceCount As Integer
Dim InvoiceTotal As Long
Dim CompanyCode As String
Dim x As Integer
'Prevent Screen flicker and Speed up code
Application.ScreenUpdating = False
'Accept Company Code from user
CompanyCode = Application.InputBox("Enter Company code")
'Start loop for Calculations and positioning
For i = 1 To 12
x = i + 6
If ActiveSheet.cell(x, 9).Value Like CompanyCode & "*" = True Then
LastInvoiceNumber = ActiveSheet.cell(x, 9).Value
InvoiceCount = InvoiceCount + 1
InvoiceTotal = InvoiceTotal + ActiveSheet.cell(x, 13).Value
End If
Next i
'pasting results to spreadsheet
Set ActiveSheet.cell(6, 13).Value = InvoiceTotal
Set ActiveSheet.cell(7, 13).Value = InvoiceCount
Set ActiveSheet.cell(6, 13).Value = LastInvoiceNumber
Application.ScreenUpdating = True
End Sub
The "if statement" seems to be the problem.
Can any one help or give me a better way (even a non-VBA way) of doing this??