Solved: Loop to check every row

[Kindly_Kaela]

Hi Everyone, and happy new year!!

Can you do a loop in VBA to check all the 'A' cells in rows 1 through 100 (for example) until it finds the right cell?

Let's say in Excel A1 = 10, A2 = 20, A3 = 30, ....A10 = 100

User enters 30 in the VBA form and I want the program to figure out that A3 = 30, and therefore the answer is B3.

This is the code I would use without a loop.

Sub ExcelRowCheck()

blah = TextBox1

Sheets("Sheet1").Select

If blah = Range("A1") Then booh = Range("B1")
ElseIf blah = Range("A2") Then booh = Range("B2")
etc.
etc.
etc.
End If

End Sub

But as you can see, this would be a nightmare if I wanted to crosscheck 100+ cells. Can you run a loop where 'X' was the number of loops and it checks all A(X)?

Hope that makes sense!

Thank you!
Kala

[XLGibbs]

Sure, pretty easy..

[vba]

Sub LoopColA()

Dim intUsersINput As Integer
Dim rngLook As Range
Dim ws As Worksheet
Dim rngFound As Range

Set ws = Worksheets("Sheet1") 'Change as needed to the right sheet name

Set rngLook = ws.Range(Cells(1, 1), Cells(100, 1)) 'Range A1:A100
'Can also be Set rngLook = ws.Range("A1:A100")

TryAgain:
intUsersINput = InputBox("Please Enter a Number") 'ask the user for input
If Not IsNumeric(intUsersINput) Then 'make sure it is a number
MsgBox "TryAgain"
GoTo TryAgain 'send them back if not
Else 'otherwise

Set rngFound = rngLook.Find(intUsersINput) 'look for the number entered in the defined range

If Not rngFound Is Nothing Then 'if it is found then
rngFound.Offset(0, 1).Value = intUsersINput 'make column B in that row = to the entered number
Else
MsgBox "Not Found" 'otherwise tell them it was not found
End If


End If


Set rngFound = Nothing: Set rngLook = Nothing: Set ws = Nothing

End Sub


[/vba]
Untested.

EDIT: Tested and works. PS. Since the cells are all contiquous and in one row, you don't need to loop through each row..which is why I did the VBA equivalent of CTRL-F to find the match.

[XLGibbs]

Also, the code I provided simply puts their input into the adjacent cell in column B

If you want to do something with the value in column B you could change

[VBA]
rngFound.Offset(0, 1).Value = intUsersINput
[/VBA]

To

[VBA]
MsgBox "The answer is:" & rngFound.Offset(0,1).Value
[/VBA]

To report the column B corresponding value to the user via a message box.

[Bob Phillips]

Just use

[vba]

With Application
result = .Index(Range("B1:B100"),.Match(test_value,Range("A1:A100"),0))
End With
[/vba]

[XLGibbs]

Just use

[vba]

With Application
result = .Index(Range("B1:B100"),.Match(test_value,Range("A1:A100"),0))
End With
[/vba]

Sure, if you want to do it the easy way

Thanks XLD, for yet another simple solution to something I overcomplexified.

[Kindly_Kaela]

Sorry, but I am still confused. And I'm not sure what to do with XLD's code.

What I'm doing exactly is....I have January 1st through December 31st down column A. When the user enters desired date, a "6" populates in column G. I wrote the following code.

Private Sub CommandButton1_Click()
Dim rngLook As Range
Dim rngFound As Range
Dim ws As Worksheet

DateEntry = TextBox17

Sheets("Database").Select
Set ws = Worksheets("Database")

Set rngLook = ws.Range("A4:A370")

Set rngFound = rngLook.Find(DateEntry)

If Not rngFound Is Nothing Then
rngFound.Offset(0, 7).Value = 6
Else
MsgBox "Did not work"
End If

Set rngFound = Nothing: Set rngLook = Nothing: Set ws = Nothing

End Sub

....I keep getting the "DID NOT WORK" msgbox.

Thanks for help guys, maybe you can walk me through my mistake?

Kaela

[XLGibbs]

[vba]
Private Sub CommandButton1_Click()
Dim rngLook As Range
Dim rngFound As Range
Dim ws As Worksheet


Set ws = Worksheets("Database")

DateEntry = DateValue(TextBox17.Text)

''Where is the text box? is it on the sheet or a user form? you have to reference it
'properly to get a value.

Set rngLook = ws.Range("A4:A370")

Set rngFound = rngLook.Find(DateEntry, LookIn:=xlValues)

If Not rngFound Is Nothing Then
rngFound.Offset(0, 6).Value = 6 'Column G is an offset of 6 columns from column A
Else
MsgBox "Did not work"
End If

Set rngFound = Nothing: Set rngLook = Nothing: Set ws = Nothing

End Sub
[/vba]
Few edits of your code...

Where is TextBox17? If it is on a user form you need to reference it with the userform name (For example UserForm1.TextBox17)

If it is not on a userform, then you should just use a cell to hold the date entered.

The more information you give, the more easily we can help with solving the issues...

[Kindly_Kaela]

No luck

TextBox17 is the date box that the user enters and it is on the same userform that this code is on. That part seems to work as I tested it with

DateEntry = TextBox17
MsgBox DateEntry

I used your code and I'm getting an error on this line:

Set rngFound = rngLook.Find(DateEntry, LookIn:=x1Values)

Here is the exact code I have in there....

[VBA]
Private Sub CommandButton1_Click()
Dim rngLook As Range
Dim rngFound As Range
Dim ws As Worksheet

DateEntry = DateValue(TextBox17.Text)

Set ws = Worksheets("Database")

Set rngLook = ws.Range("A4:A370")

Set rngFound = rngLook.Find(DateEntry, LookIn:=x1Values)

If Not rngFound Is Nothing Then
rngFound.Offset(0, 5).Value = 6
Else
MsgBox "Did not work"
End If

Set rngFound = Nothing: Set rngLook = Nothing: Set ws = Nothing

End Sub
[/VBA]

[XLGibbs]

YOu have Lookin: = x1Values

It should be lookin: = xlValues

Small L not a Number One

[Kindly_Kaela]

OK, for whatever reason the error message stopped. But I am still getting "DID NOT WORK" msgbox. See code above.

[XLGibbs]

OKay, then what is happening is it is comparing the datevalue of whatever is in TextBox17 against the range and not finding a match. That means that the values in the range are not dates.

If they are text, you can remove the Lookin:=XLValues part, and remove the DateValue() wrapper...

What you can is in the line:

Set rngFOund =

Click on that line and hit the F9 key. It will set a break point. When you are testing the code, it will stop and take you to that line.

Use the F8 key to "step through" the code. When the line is highlighted in yellow you can cursor over the DateValue variable and see what is stored there. May give you a clue as to what it is looking for as opposed to the format of the cells in A4:A340

When I tested the code, it found the dates entered in column A no problem, but my column A is for sure Dates...(not the text string of a date)

[Kindly_Kaela]

WHOOHOOO it works!!

I had the excel spreadsheet formated to show Dec 30 instead of 12/30/2006 (which is the text box output). When I changed the Excel format to 12/30/2006 it worked.

I probably used a strange way to figure out today's date. I used =Today() in excel and referenced that in TextBox17.

[VBA]
Sheets("Formulas").Select
DateEntry = Range("I11")
[/VBA]

It defaults to today's date unless the user changes the date. But no matter what formate I put excel in for that cell that is =Today(), it always comes out as m/d/yyyy Any suggestions as I'm sure I'm making this much more complicated then it needs to be. LOL!

Oh well, even if no suggestions, thanks so much for your help XL!!

[XLGibbs]

If you want it to work regardless of the display, make sure they are dates and use

Lookin:=xlFormulas

The formula for dates is a 5 digit number, and the DateValue() wrapper will convert dates to that 5 digit number.

In this case, it was comparing the displayed values

[XLGibbs]

I assume your TextBox17 value is the Range("I11")

So you can just as easily refer to that cell as the source of the date to look for.

Also, avoid using .Select statements, you really don't need them

You can do this:

[VBA]

DateValue = Sheets("Formulas").Range("I11") 'to populate the variable

'or
TextBox17.Text = Sheets("Formulas").Range("I11").Text 'to populate the text box


[/VBA]

[XLGibbs]

It defaults to today's date unless the user changes the date. But no matter what formate I put excel in for that cell that is =Today(), it always comes out as m/d/yyyy Any suggestions as I'm sure I'm making this much more complicated then it needs to be. LOL!

Oh well, even if no suggestions, thanks so much for your help XL!!

Format the cell using Format>Cell>Date and select your desired format or

in code you would use Format(xxxxx,"mm-dd-yyyy") for that format.. or whichever format you choose.

Remember that no matter how it is displayed, Excel stores dates as the number of days since 0/0/1900 (1 as a date would 1/1/1900)

[XLGibbs]

YOu can also just populate the date on the forms load event with:

[vba]
TextBox17.Text = Format(now(),"mm/dd/yyyy")[/vba]

[Bob Phillips]

... or just use

[vba]

Me.TextBox17.Text = Range("I11").Text
[/vba]

it will retain the format in the worksheet cell.

And you really should get out of the habit of leaving controls to the default name, far better to adopt a naming standard and give it a meaningful name.

[XLGibbs]

... or just use

[vba]

Me.TextBox17.Text = Range("I11").Text
[/vba]
it will retain the format in the worksheet cell.
.

Forgive me if I am wrong, but with multiple sheets involved, in her case "Database" and "Formulas" I believe, wouldn't leaving the object reference off of the Range("I11").Text line cause issues ...which is why I proposed:
TextBox17.Text = Sheets("Formulas").Range("I11").Text 'to populate the text box
along the lines of what you are saying.

Excellent point on :

And you really should get out of the habit of leaving controls to the default name, far better to adopt a naming standard and give it a meaningful name

It makes things WAY easier in the long run to get into the habit of naming objects..

[Bob Phillips]

Forgive me if I am wrong, but with multiple sheets involved, in her case "Database" and "Formulas" I believe, wouldn't leaving the object reference off of the Range("I11").Text line cause issues ...

Of course it would, I was just pointing out the difference between .Value and .Text to Kaela.

[Kindly_Kaela]

Thank you so much Gibbs and D! I'm so new to VBA and grateful for every tip!

Happy New Year Guys!!