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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

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,127,530
Messages
5,625,356
Members
416,096
Latest member
forevans

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