Here is my code it should find the only cell in Sheet 3 that contains the words ie: "Total Joe Painting"
After it copy the cell 2 to the right of it. However that is not the problem right now when I change the value of Sheet1.Cells(2,2) it does not correctly go to that field instead it still only finds the first cell with Total.
Please Login or Register to view this content.
Cell 2,2 is a user inputted Business Name. And will change frequently.
Last edited by DadaaP; 06-03-2013 at 07:56 PM.
What triggers the sub to run?
Entia non sunt multiplicanda sine necessitate
A button, is that what your asking?
A user inputs his data into the field 2,2 and then presses button that triggers macro. Then he should be able to enter another name and it find the other name and represent the coresponing number. However it still references the first one.
DadaaP,
The code works correctly in my test. I notice one possible reason you might be getting those results.
You are referencing the sheets using a mixture of Sheet Names, like: Worksheets("Sheet3").Activate
and CodeNames like: Sheet1.Cells(2, 2).Text and Sheet3.Cells(1, 1)
Those are two separate mapping systems and it's possible through copying, deleting and renaming sheets in your workbook that
Sheet1.Cells(2, 2).Text is not referencing Worksheets("Sheet1")
If Sheet1.Cells(2, 2).Text is referencing "Joe Painting" in Worksheets("Test1"), then you would get the results you describe.
You can check this in the VBE Project Explorer to see if you have a Sheet Object icon that reads
Sheet1 (Sheet1)
or
Sheet1 (Something else)
Regardless of whether that is the cause of your current problem, I'd suggest sticking with one method of referencing sheets unless you have a good reason to do otherwise.
Last edited by JS411; 06-04-2013 at 03:25 AM.
When I look at my Project Explorer I think I found the problem you are speaking of.
I see:
Microsoft Excel Objects
..Sheet1(Sheet3)
..Sheet2(Sheet1)
..Sheet3(Sheet2)
..ThisWorkbook
I would assume this is the case for my problems, do I change them back to Sheet1(sheet1) and so on and so forth?
I am not sure what you are speaking of when you say different methods to reference.
After changing the above problems to correspond the sheets correctly I now get an error code: Object variable or With block variable not set
Please Login or Register to view this content.
That means the Find isn't finding anything to activate.
If posting code please use code tags, see here.
Never mind works perfect! thank you I followed what you said and addressed the method problem. Works flawlessly now!
Yeah I am doing some research to handle such
Please Login or Register to view this content.
Is there a value If Find returns an error than I can input a 0 into that field? Since there is no value to begin with I just want it to return 0. Sounds like a If then statement.
If you want to check if Find has worked you can try something like this.
Please Login or Register to view this content.
Used a modified version of this to loop and then loop to check if it has the receiving amounts. Works GREAT! Norie thank you so much for the loop help! And JS for getting me off to the right track.Originally Posted by Norie
If you want to check if Find has worked you can try something like this.
Please Login or Register to view this content.
I am still learning the mapping system but is it bad to say that this VBA code is quite enjoyable! Spend a few hours creating a vba program and I save myself 15 hrs a month
Can we add solved tags?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks