If Then Statement - Still Not Working

Hi,

I can't see an 'End If' instruction to close the 'If' Statement. That might be your problem.

Rgds

Richard,

I did have a End If, but I forgot to copy it into the code. Here I revised the code with the End If.

Please Login or Register  to view this content.

The debugger highlights my first line in the code every time, and I can not figure out why. It looks okay to me based on some other stuff I have and seen. Please help.

Mudraker,

Could you give me a hint on the quotation marks in my code. I am not sure. I have tried it a few different ways. Still not working - SEOT

For one thing, you are missing the row number for Range(AC).
e.g.

Please Login or Register  to view this content.

I tried to read into the code you gave me, and I think my code runs a little further now. It went through some of the instructions now, and it gives me a 424 error though. I am no longer getting my code flagged on the first line though. It looks like progress. Do you know what a 424 error is???

Here is my new code:

Please Login or Register  to view this content.

Rule of thumb, do not use []'s when you are building an object with variable values. Also, try not to mix types like [], Range and cell. It is better to use the fully qualified Workbooks("somename.xls").WorkSheets("somesheetname").Range("somerangename"). This lets intellisense work for you.

Of course it is perfectly fine to use []'s when you are building a string for your formula. When I build a string for formulas, I do it manually, copy it and then use Debug.Print to verify that I built the proper formula string.

Your ranges are still not using a column and a row number. If you are using variables for it, they must evaluate to a valid range.

Always use Option Explicit at the top of your Module.

I am unclear as to what your While Loop is doing. You have an x=y=z comparison.

Of course you would need Text.xls open for this code to work.

Please Login or Register  to view this content.

Hi

What are you trying to do with that first If statement

Please Login or Register  to view this content.

in particular what are the square brackets supposed to be referring to, and what's the reference to DefName? Is that a Workbook name variable. And then the other thing I can't understand is that you seem to be comparing a column range "AC" with a single cell .Cells(7,2) which doesn't seem to make sense.

In any case the format of the full line would normally be something like

Please Login or Register  to view this content.

HTH

Kenneth,

I used the brackets[] because I found some code to pull in a file. It worked with part of my project, but I can not get my new code to work. I do not understand everything you are telling me, but I know this works with some other stuff I have. I am not sure if it is killing my new code though

Richard,

I am trying to compare one cell value to an entire column in another spreadsheet. Is there another way to do this? Am I not doing this right??

Thank you both for your time and effort. I am going to keep working on it tonight some and tomorrow. Hopefully, I can get it working some how....- SEOT

I believe all my other code is correct. I believe my If Statement is the only problem in my code. Most the other code worked before I added my the If Statement.

Richard,

You might have hit the nail on the head. I might not be comparing one cell to the entire column exactly right. I was trying to compare the one cell in a spreadsheet against an entire column in another spreadsheet. Then, I wanted to do something until I found something different in some other columns. Do you have any examples of such a thing or the language to use. I am used to being able to find this type of stuff on the internet, but VBA does not have much out there for you to look at. thanks - SEOT

Hi,

OK, if I now understood the requirement correctly I think I'd be inclined to put your If statement in a For..Next loop, where the loop variable is the number of rows in the AC column in which you're wanting to compare each cell with your activesheet cells (7,2)

Furthermore I'd be inclined to declare a variable for your active(7,2) cell to avoid VBA having to evaluate it each time round the loop - there will be a small time overhead involved otherwise. So (untested) but something like...

Please Login or Register  to view this content.

HTH

Richard,

I am going to try what you mentioned a little later tonight and some tomorrow. I have more time on the weekend to work on this! I will let you know what I find out! I really appreciate all your help! You all are the greatest!!! - SEOT

Richard,

I entered all the code you gave me above. Now, I am getting a run time error '9' on the line below.

Please Login or Register  to view this content.

I am currently trying to research what a run time error '9' is, but I am not getting much luck so far. Do you have any ideas??? I am getting tired now, and I might have to pick it up tomorrow. I might look a little bit longer though....

I am trying to go about this in a different way now. I could never get my original idea to work so I have something else. I think you can answer this question easier. Here is what I have:

Please Login or Register  to view this content.

This code works partially like my other code posted above, but I still have the same issue as before too(just differently done now). I just need to get startRow and endRow in the bottom most portion of this code. I have want I am looking for commented out at the bottom of this code, but it does not work. Could you please help me ???? Thank U All!!! - SEOT

I really appreciate all the help from everyone so far!!! I believe I am close to what I need now. I appreciate the last little code segment from SHG because I was able to use that in what I have now. Here is what I have so far in the part I have been working on:

Please Login or Register  to view this content.

Now, I am stuck on getting the values for cell1, cell2, cell3, and cell4 in the a small section of the code above. Here is the code I want the values of cell1, cell2, cell3, and cell4 to work in.

Please Login or Register  to view this content.

I am trying to replace to commented out section of code with the RC referencing.

Please HeLp - SEOT

Hi,

Without working through all the code the error you're probably making is not converting cell1 cell2 etc to an address string. Note that in your "=SUMIF([" ...etc line of code that you are using character strings. So you need to use either

Please Login or Register  to view this content.

or alternatively when declaring the Cell1 values declare them as strings in the first place and then use

Please Login or Register  to view this content.

Then ensure you build up your SUMIF statement with the appropriate addition of the string values.

HTH

I think the code above will work if I was using the Activeworkbook, but I am using the other workbook that I access in the code above using Workbooks(DEFName)Sheets("Sheet1"). Do you know how to combine the code you just gave me above with the other workbook inestead of the ActiveWorkbook. I can't seem to get it right still. I keep trying many ways to combine it. - SEOT

Hi

What is DefName?

I can't see where it's been declared and set so I'm assuming it's the actual name of a workbook. In which case I think you need to put quotes around it.

HTH

It would be easier to help if you posted an xls. You post snippets where we can't tell what all is going on.

Somewhere, you must be using:

Please Login or Register  to view this content.

This code is in a module that I call into the other pages of the workbook so I can compare information in one spreadsheet against another.

Please Login or Register  to view this content.

Do you know how I can incorporate the code given in post #20 with the Workbook named DEFName? I still can not figure this out. Some of this stuff I can not find on the Web or in the books I have. Thanks - SEOT

Please help me figure this last piece out. I can not figure it out. Anyone know how to do this???? thank you - SEOT

I have some code that does not work exactly like I want. I need to make a few changes in it. Here is the code I am speaking of:

Please Login or Register  to view this content.

I am trying to alter a small part of the code above. I want to make changes in the following lines:

Please Login or Register  to view this content.

I am trying to replace the code commented out with the code below it, but it is not accepting the cell1, cell2, cell3, and cell4 variables.

Richard Buttrey said something about using the following code in place of what I have for the cell1 code and the others(cell2, cell3, cell4):

Please Login or Register  to view this content.

This code does not work though. I need to have the DEFName included in it because the DEFName references the other file I am going through. How do I incorporate the DEFName into Richard's code above. I envision something like this

Cell1 = Workbooks(DEFName).Sheets(Sheet1").Cells(startRow, 9).Address


but this does not work. I don't know how to combine it with the other sheet. I believe this code Richard gave me would work with the ActiveSheet, but I can not get it to work with the other sheet I am comparing things with. I am not sure how to get this to work. Please help me out here. - SEOT

I think you have the idea for solving this, but I am getting an error on one thing. I get an error on this line:

Please Login or Register  to view this content.

It looks like it is working all except this line above. I get an error message that says Run-time error '1004'. Do you know what this means????

After we get rid of this error, I think it will run? Do you know what could be wrong?? Thank U - SEOT

SHG,

It looks like the formula is doing right, but I can not get the one line mentioned above to work. The one line makes sense, but it does not work. I would like this one line to go through the cells in the active sheet and gather the information from the other sheet. What you have makes sense, but it does not work. This programming language is really picky! Other languages do not seem to be sooo picky! I am still having issues with it, and I worked on it some last night and this morning. - SEOT

When I run the debug statement, the information printed out in the immediate window looks right. I just need to populate this in the cells of the ActiveSheet. The formula you have for this(below) looks fine, but it does not work.

Please Login or Register  to view this content.

I want everything put in sForm in the ActiveSheet. Your code looks like it should work but it does not. Do you have any more ideas??? Thank U!! - SEOT

The formula looks good, but it is not right! When I paste it into the ActiveSheet, it does not display the path of the DEFName file like my ole formula used to do. It is picking up the name of the workbook I am opening, but it does not have the path in the formula like it should. How can I get it to pick up the path of the DEFName file???? It should have the path in it too! thanks again for taking time with me- SEOT

The immediate window has this

=SUMIF([1124LaborP9S4.xls]Sheet1!I108:I154, BAKER, W*, [1124LaborP9S4.xls]Sheet1!AB108:AB154)

It used to have something like this and it worked before I decided to make some changes to the code to do more things

=SUMIF([1124LaborP9S4.xls]Sheet1!$I$1:$I$2000,"BAKER, W*",[1124LaborP9S4.xls]Sheet1!$AB$1:$AB$2000)

Do you know what I could be doing wrong??? Thank U SHG for everything you have done so far though! I do appreciate the help!!

You have to add quotation marks around the person's name to make it work. It should be

=SUMIF([1124LaborP9S4.xls]Sheet1!I108:I154, "BAKER, W*", [1124LaborP9S4.xls]Sheet1!AB108:AB154)

in the cell instead of

=SUMIF([1124LaborP9S4.xls]Sheet1!I108:I154, BAKER, W*, [1124LaborP9S4.xls]Sheet1!AB108:AB154)

How do you code this into VBA though? I have tried to fix it, and I can not figure that part out.- SEOT

Originally Posted by shg

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I get a #NAME? in all the cells I am trying to populate after I do this. It looks like we are getting closer, but it still does not work right....

I think I know why we have a name error. We have single quotes in the formula around the range. Do you know how to take these off? See....

=SUMIF([1124LaborP9S4.xls]Sheet1!'I108':'I154', "BAKER, W*", [1124LaborP9S4.xls]Sheet1!'AB108':'AB154')

It is around the I108:I154, and the AB108:AB154. We need to get rid of the single quotes in the code. How do you do that???

sRef does not contain single quotes. Here is something funny though. The immediate window has the formula right. I can use the formula out of the immediate window in the cells, and it will work. The cells in my spreadsheet have it with single quotes though after the code goes though, and it does not work there. - SEOT

Working on stripping it now. Also, I have to eat dinner. I will have it to you shortly. Thank U!!! - SEOT

I was wondering if you got a chance to look at this yet?? thanks - SEOT

Shg,

I forgot to mention something. You have to open the FOR Excel_Forum spreadsheet and cut and paste the drive and path to the other spreadsheet(1124LaborP9S4) in the PATH tab before you click the button in the FOR Excel_Forum spreadsheet. This is probably the issue you have with it. I did not think of that when attaching the two spreadsheets. Could you please take a look at it for me. After you place the path and directory of the spreadsheet, you should understand it right away!!! Thanks again - SEOT

Thank U SHG!!!! I really really really appreciate this!!! I am so thankful you continued to help until you solved it!!! This will save me some time during the day filling out a spreadsheet!!!