VB and Excel weirdness

Bulbasaur

Hey, so, I've been working on automating a data processing process to fill out all sorts of government forms with data from other excel files to save me and co-workers literally hours and hours of work and despite my limited understanding of programming in general I've managed to cobble together a program that actually does what it's supposed to do... sorta.

Basically, the first step of my program requires that I determine what data is in which column (since unfortunately there was no standardization in the layout of these forms.).

Let me take a step back and explain something first... since I'm an extremely amateur programmer and I don't really have the time nor the desire to learn much more I kinda came up with the idea to just copy and paste the relevant data from the excel file with the data onto the sheet with the form that requires filling out as I have no idea how to draw data from a seperate excel file (That's not the problem just necessary background)

Now once copied and pasted I use the following code to determine what data each column refers to

Do
If Cells(counterx, counterz) = "Unit" Or Cells(counterx, counterz) = "UNIT" Then
    unity = counterz
ElseIf Cells(counterx, counterz) = "Tenant" Or Cells(counterx, counterz) = "TENANT" Then
    tenant = counterz
ElseIf Cells(counterx, counterz) = "SQ.FT." Or Cells(counterx, counterz) = "Sq.Ft." Or Cells(counterx, counterz) = "SQFT" Or Cells(counterx, counterz) = "SQ.FT" Or Cells(counterx, counterz) = "sqft" Then
    SQft = counterz
ElseIf Cells(counterx, counterz) = "$/SQ.FT." Or Cells(counterx, counterz) = "$/SQ.FT" Or Cells(counterx, counterz) = "$/Sq.Ft." Or Cells(counterx, counterz) = "$/Sq.Ft" Then
    dollarPerSQFT = counterz
ElseIf Cells(counterx, counterz) = "Rent" Or Cells(counterx, counterz) = "RENT" Then
    rent = counterz
ElseIf Cells(counterx, counterz) = "CAM" Or Cells(counterx, counterz) = "Cam" Then
    CAMMY = counterz
ElseIf Cells(counterx, counterz) = "RTAX" Or Cells(counterx, counterz) = "Rtax" Or Cells(counterx, counterz) = "RTax" Then
    RTAX = counterz
ElseIf Cells(counterx, counterz) = "Start" Or Cells(counterx, counterz) = "START" Then
    commence = counterz
ElseIf Cells(counterx, counterz) = "Expire" Or Cells(counterx, counterz) = "EXPIRE" Or Cells(counterx, counterz) = "Expiry" Or Cells(counterx, counterz) = "EXPIRY" Then
    expiry = counterz
ElseIf Cells(counterx, counterz) = "OPTIONS" Or Cells(counterx, counterz) = "Options" Or Cells(counterx, counterz) = "OPTION" Or Cells(counterx, counterz) = "Option" Then
    options = counterz
End If

counterz = counterz + 1

Loop While Cells(counterx, counterz) <> "NOTES / PERCENT RENT"

(counterx is a variable determined earlier in the program that automatically locates the row which the data was copied into and I've already verified that it is indeed working as intended.)

Which works fine in all cases except that for some reason whenever I remove old data and paste the new data to be processed, the If statement refering to "CAM" stops working. The weirdest part is that the problem is easily fixed by simply retyping the word CAM into whichever column was headed by it.

I'm 100% certain that CAM is properly capitilized the first time and that the cell isn't actually hiding some formula. I can't figure out for the life of me what would be causing this problem and I guess it's not really a big deal since it's easily fixed but I'm trying to make the macro as user friendly as possible since my coworkers aren't really very computer savvy at all.

Anyone have any idea what could be causing this?

bowen

It's been a long long time since I've messed with VB6, but, to make things easier on yourself, couldn't you do something like this:

If LCase$(Cells(counterx, counterz)) = "cam" Then
    unity = counterz

Since you might be running into something like "cAM" or something equally stupid but hard to notice. Just an idea, I can't see anything that sticks out of the ordinary, though.

Cauld

It could be that there's extra spaces at the end of your string, try adding a left([cell reference],3) in there. Like this I guess:

If LCase$(left(Cells(counterx, counterz),3)) = "cam" Then
    unity = counterz

Jeice

You should add the LCase$(left(<cell>)) to all your if statements. It makes your code more pretty, and it will be more dynamic. Just because "CAM" is the only one screwing you up right now doesn't mean the others won't screw up in the future.

Also, to help debug, you can use the MsgBox function to see what's actually in the cell (to check for extra spaces and stuff)

MsgBox cell(counterx, counterz).value

focused7

Could be catching some weird spaces or case issues. Have you thought about cleaning up the code some using a Select Case instead?

CellValue = Ucase(Trim(Cells(counterx, counterz)))

Select Case CellValue
Case is = "UNIT"
  unity = counterz
Case is = "TENANT"
  tenant = counterz

etc, etc

End Select

whuppins

As with the previous posts, I also suggest that you simplify the way you are trying to match text. Not just because it's more elegant, but also because it leaves less room for human error and makes it easier to troubleshoot. I'd write each case similar to this:

If Not Cells(counterx, counterz).Find("$/SQ.FT", LookIn:=xlValues) Is Nothing Then dollarPerSQFT = counterz

This will do a case-insensitive search through the specified cell's contents to see if it contained the specified string. The difference with my code is that it doesn't have to be an exact match -- in other words, the possibility of a trailing period (.) in the above example doesn't require you to specify a separate case. All four cases you've listed for the $/Sq. Ft. column are accounted for in my example.

As I said, this may not solve your problem, but simpler code should make it easier to troubleshoot, and you won't have to worry about leading spaces or different cases, etc.

Other thoughts:

-It might help to declare your variables at the beginning, even though VBA doesn't require you to do so. Just insert a line like this:

Dim unity, tenant, SQft as integer

at the top. Include all your variables, of course, not just three.

-If you're going to continue to reference cells' contents using Cells(), you should really hang a .Value off the end of each reference, especially if you're working with named ranges. So instead of leaving it as:

ElseIf Cells(countex, counterz) = "Rent" ...

...it should be:

ElseIf Cells(countex, counterz).Value = "Rent" ...

...another thing that VBA doesn't require you to do, but probably should since it can lead to snags similar to this one. Note that this tip doesn't apply if you're referencing a cell as part of the Find method above.

- Also, you may want to do away with the nested block of If/ElseIf statements. Unless the code you shared is part of a larger, more complicated block you've written, you can probably just run a slew of If statements (one line each, no "End If" necessary) all in sequence.

Here's a re-write based on the above:

Dim unity, tenant, dollarPerSQFT, SQft, rent, CAMMY, RTAX, commence, expiry, options as integer
Do
    If Not Cells(counterx, counterz).Find("UNIT", LookIn:=xlValues) Is Nothing Then unity = counterz
    If Not Cells(counterx, counterz).Find("TENANT", LookIn:=xlValues) Is Nothing Then tenant = counterz
    If Not Cells(counterx, counterz).Find("$/SQ.FT", LookIn:=xlValues) Is Nothing Then
        dollarPerSQFT = counterz
    Else
        If Not Cells(counterx, counterz).Find("SQ.FT", LookIn:=xlValues) Is Nothing Then
            SQft = counterz
        Else
            If Not Cells(counterx, counterz).Find("SQFT", LookIn:=xlValues) Is Nothing Then SQft = counterz
        End If
    End If
    If Not Cells(counterx, counterz).Find("RENT", LookIn:=xlValues) Is Nothing Then rent = counterz
    If Not Cells(counterx, counterz).Find("CAM", LookIn:=xlValues) Is Nothing Then CAMMY = counterz
    If Not Cells(counterx, counterz).Find("RTAX", LookIn:=xlValues) Is Nothing Then RTAX = counterz
    If Not Cells(counterx, counterz).Find("START", LookIn:=xlValues) Is Nothing Then commence = counterz
    If Not Cells(counterx, counterz).Find("EXPIR", LookIn:=xlValues) Is Nothing Then expiry = counterz
    If Not Cells(counterx, counterz).Find("OPTION", LookIn:=xlValues) Is Nothing Then options = counterz
    counterz = counterz + 1
Loop While Cells(counterx, counterz).Value <> "NOTES / PERCENT RENT"

Notes:

I was lazy, so I left in some nested IFs to sort out the "SqFt" vs. "Sq.Ft" vs. "$/Sq.Ft" vs. "$/Sq.Ft." mess. Not an ideal solution, but it should still be more straightforward than what you're working with.

Note that the expire/expiry and option/options issues are handled by specifying the proper search string. This is the benefit of using the Find() method as opposed to searching for an exact cell value.

Bulbasaur

Just want to say thanks to all of you guys I used your suggestions and it completely fixed my problem. Ha, if you guys saw the rest of my code though you'd be mortified. It's... ugly. Guess that's why I'm sticking to my day job!