setting variable equal to a value in a cell

Hello-

New to VBA after having just finished an introductory text. Thanks for your help in advance!

I am trying to do something that I thought would be pretty simple- set a variable equal to a cell in a spreadsheet.

the line of code that is giving me trouble is:

payrate = Worksheets("sheet1").Cells(payrow, paycolumn)

The values for payrow and paycolumn are determined elsewhere in the program. For example, if payrow is determined to be 1 and paycolumn is determined to be 10, I want payrate to be equal to the value in the cell at 1,10 (row 1 column 10) in sheet 1.

When the debug function is running, I hover my mouse cursor over payrow and paycolumn and the values in those variables are correct, but hovering over payrate shows a value of 0. The error I am getting is "run time error 1004, application defined or object defined error"

What am I doing wrong? Thanks for your time.

Can you post all your code?

paycolumn should be declared as Long, not String (not sure if that is causing your error though).

Nonetheless, you should declare variables correctly as it can cause other problems.

What is in the relevant cell?

Originally Posted by StephenR

Nonetheless, you should declare variables correctly as it can cause other problems.

What is in the relevant cell?

Will do.

The relevant cell could be any one in a range from AB1 to AM4 or AB7 to AM10 depending on the user's input. In the example I was playing with in debug, payrow is showing equal to 1 (correct), paycolumn is showing 39 (correct), so I want to say payrate to the value in row 1, column 39, which should be cell AM1. The value in cell AM1 is 254.74. payrate shows a value of 0 when I hover over it in debug, and I get the error message as in post 1.

There's nothing wrong with that line as long as the variables are correctly declared, and the value in the cell is a number.

I'm stumped. Can you post a workbook?

Sure.....don't laugh as I am a hack!

to get the same numbers as me select:
Captain
747/777/787/767-400 option
30 years of service
200000 gross pay
900 hours work
10 days vacation
17000 401K
Yes 50 or older

If I declare paycolumn correctly, I don't get an error, although I can't tell if the code does anything.

Originally Posted by Norie

The value of payrow is 0 because none of the expressions in these If statements evaluate to True.

Please Login or Register  to view this content.

1) In debug mode, when I hover over OptionCaptain and Option747, they both show TRUE so therefore payrow should be set to 1
2) When I hover over the payrow variable, it shows 1, which it is supposed to be.

I guess I don't understand why payrow wouldn't = 1 if both OptionCaptain and Option747 are both TRUE?

EDIT: Just saw your response disregard.

Originally Posted by StephenR

If I declare paycolumn correctly, I don't get an error, although I can't tell if the code does anything.

Yeah, when I DIM paycolumn as long, the button on my User Form doesn't do anything and I don't know why. When I DIM paycolumn as string, the button works but I get the error as described in post 1.

Norie - does for me.

The problem must lie elsewhere.

It's working through a whole load of calculations but without knowing what should happen, it's hard to know where the problem lies.

Originally Posted by Norie

I think the problem might be with one/some of the other variables, for example daysofvacationforfeit is never set, so the vacationvalue calculation might be off.

Originally Posted by StephenR

It's working through a whole load of calculations but without knowing what should happen, it's hard to know where the problem lies.

Yeah, sorry guys. It's a work in progress and probably poorly written at that as it is my first attempt at writing something using VBA. I don't want to waste your time. What's supposed to happen is that a person enters the data on the Excel spreadsheet above the click here button. Then the userform should pop up when the click here button is pressed. Then the data is entered/selected into the userform, then after hitting the button on the userform the program is supposed to crunch numbers based on the entered data on the userform and the data on the spreadsheet then go back to the worksheet 1 and enter data into F14-f16. After I got it working, I was going to make it more "pretty" with graphs and colors and stuff.

But the program hasn't gotten that far yet because I can't get that payrate variable to set properly, hence the initial post.

Originally Posted by Norie

Are you sure payrate isn't being set properly?

When I step through the code it's definitely getting set to something.

I am sure of nothing! payrate should be set to a number like 254.77 not 0, like I was getting.

How does one "step through the code" so that one can see what the values of the variables are?

When I run it, this line is false

Please Login or Register  to view this content.

so much of the code is missed.

Originally Posted by Norie

First put a breakpoint in the Click_Here_Click sub by selecting line of code and hitting F9, then run the userform, enter the required informationan an click th Click Here button.

You should now go into break mode with the code paused on the line of code you put the breakpoint on and you can then step through the code using F8.

Originally Posted by StephenR

When I run it, this line is false

Please Login or Register  to view this content.

so much of the code is missed.

Thanks guys. Had to go to sleep and now working all night, so have to put this down for a day or so. I will look at this information and post back if you guys haven't gotten bored yet!

It's late, I'm tired and not 100% sure what has happened since the opening post here but.....shouldn't:

payrate = Worksheets("sheet1").Cells(payrow, paycolumn) 'page 118


be:

payrate = Worksheets("sheet1").Cells(payrow, paycolumn).value
if you want the value of the cell being returned as payrate? If I've misunderstood or am talking tosh feel free to mock me once I'm in bed!

Originally Posted by malcmail

It's late, I'm tired and not 100% sure what has happened since the opening post here but.....shouldn't:

payrate = Worksheets("sheet1").Cells(payrow, paycolumn) 'page 118


be:

payrate = Worksheets("sheet1").Cells(payrow, paycolumn).value
if you want the value of the cell being returned as payrate? If I've misunderstood or am talking tosh feel free to mock me once I'm in bed!

Thanks malcmail. Unfortunately, adding the ".value" to the end didn't work

Originally Posted by Norie

First put a breakpoint in the Click_Here_Click sub by selecting line of code and hitting F9, then run the userform, enter the required information and click the Click Here button.

You should now go into break mode with the code paused on the line of code you put the breakpoint on and you can then step through the code using F8.

Originally Posted by StephenR

When I run it, this line is false

Please Login or Register  to view this content.

so much of the code is missed.

Originally Posted by malcmail

It's late, I'm tired and not 100% sure what has happened since the opening post here but.....shouldn't:

payrate = Worksheets("sheet1").Cells(payrow, paycolumn) 'page 118


be:

payrate = Worksheets("sheet1").Cells(payrow, paycolumn).value
if you want the value of the cell being returned as payrate? If I've misunderstood or am talking tosh feel free to mock me once I'm in bed!

Stephen and malcmail and Norie-

After stepping away from my little program for a while and coming back to it, I figured out some errors and what you were all trying to tell me. When I properly DIM'ed the variables as you described, the code was running and fixed, but I just didn't know it because a lot of it was being bypassed in the error described above. Now that I know how to step through the code F8/F9, I can see what was causing my confusion. Now I'm working on the next set of errors Thanks very much for your time.

Just tried it myself without value and it worked. Hoping you've found your answer now.

Glad we got there.

maclmail - arguably it's better practice, but because Value is the default property of the Range object in most cases we don't strictly need to specify it.

StephenR

THanks for the info there. I've always done it that way simply as I remember the first time I ever tried anything it wouldn't work without it. Good to know for future.

Yes there are some cases when you explicitly have to include it, and I've never understood why.