VBA code for comparing user input string with cell content

Rothoik

New Member
Joined
Apr 25, 2013
Messages
2
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??
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Rothoik

New Member
Joined
Apr 25, 2013
Messages
2
Thanks for your swift response, I corrected a few other things, all "schoolboy errors" and now it works.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,123,103
Messages
5,599,740
Members
414,333
Latest member
willfrederick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top