PDA

View Full Version : Solved: Loop to check every row

Kindly_Kaela
12-30-2006, 11:12 AM
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
:cloud9:

XLGibbs
12-30-2006, 11:21 AM
Sure, pretty easy..

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:
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
End If

End If

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

End Sub

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
12-30-2006, 11:28 AM
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

rngFound.Offset(0, 1).Value = intUsersINput

To

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

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

Bob Phillips
12-30-2006, 12:02 PM
Just use

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

XLGibbs
12-30-2006, 12:04 PM
Just use

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

Sure, if you want to do it the easy way

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

Kindly_Kaela
12-30-2006, 12:14 PM
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
:cloud9:

XLGibbs
12-30-2006, 12:39 PM
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

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
12-30-2006, 12:50 PM

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....

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

XLGibbs
12-30-2006, 12:51 PM
YOu have Lookin: = x1Values

It should be lookin: = xlValues

Small L not a Number One

Kindly_Kaela
12-30-2006, 12:53 PM
OK, for whatever reason the error message stopped. But I am still getting "DID NOT WORK" msgbox. See code above.

:dunno

XLGibbs
12-30-2006, 12:58 PM
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
12-30-2006, 01:02 PM
WHOOHOOO it works!! :clap:

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.

Sheets("Formulas").Select
DateEntry = Range("I11")

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!!
:beerchug:

XLGibbs
12-30-2006, 01:04 PM
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
12-30-2006, 01:07 PM
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:

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

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

XLGibbs
12-30-2006, 01:10 PM
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!!
:beerchug:

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
12-30-2006, 01:19 PM
YOu can also just populate the date on the forms load event with:

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

Bob Phillips
12-30-2006, 01:43 PM
... or just use

Me.TextBox17.Text = Range("I11").Text

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
12-30-2006, 01:49 PM
... or just use

Me.TextBox17.Text = Range("I11").Text

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
12-30-2006, 03:16 PM
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
12-31-2006, 04:10 AM
Thank you so much Gibbs and D! I'm so new to VBA and grateful for every tip!

Happy New Year Guys!!

XLGibbs
12-31-2006, 07:36 AM
Likewise Kaela!:bigdance2

Kindly_Kaela
12-31-2006, 10:51 AM
Hi Again,

I am trying to learn Gibbs' recommended way to handle sheets. What is wrong with this code? I want to 'clear contents' of all these cells.

Private Sub CommandButton1_Click()

Sheets("Formulas").Range("F4:N368,P4:V368,X4:AA368").Select
Selection.ClearContents

End Sub

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

You can do this:

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

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

XLGibbs
12-31-2006, 10:55 AM
Sheets("Formulas").Range("F4:N368,P4:V368,X4:AA368").ClearContents

Should work just fine.

In general if you record a macro, and it says

someting.Select
Selection.somethingelse

You can eliminate the Select and Selection and just have

something.somethingelse

Bob Phillips
12-31-2006, 10:55 AM
It will fail if the Formulas sheet is not active, so activate it first.

Also, no need to select.

Sheets("Formulas").Activate
Range("F4:N368,P4:V368,X4:AA368").ClearContents

XLGibbs
12-31-2006, 10:58 AM
Sheets("Formulas").Range("F4:N368,P4:V368,X4:AA368").ClearContents

xld:
Unless I am missing something, the above works without activating the sheet, even if another sheet is active... I am I crazy or am I missing something...

Bob Phillips
12-31-2006, 11:04 AM
It does, but I was making the point as to why Kaela's code might of failed, and then added the selection removal as extra info. I felt it better to inform her why her code might fail rather than just give an alternative solution.

XLGibbs
12-31-2006, 11:09 AM
It does, but I was making the point as to why Kaela's code might of failed, and then added the selection removal as extra info. I felt it better to inform her why her code might fail rather than just give an alternative solution.

:think: Probably a good idea. One I used to employ as well. THanks! :thumb