Hello, in VBA, I am trying to find a value in a cell using Range("A1").Value
The problem I'm running into is "A1" contains a formula displaying the value. If I copy and paste A1 as just text the code works, but that's not a solution. The cell needs to remain variable. Any way to get VBA to recognize the text written in the cell?
I don't follow what you are asking. Can you elaborate?
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
I think the OP is saying
A1 contains a formula.
Using Range("A1").value results in...presumably the formula not the value ? (Insert your answer here OP)
However if the OP overwrites the value in A1 using copy and paste values the same Range("A1").value codes results in the expected value.
Am not sure why Range("A1").value would return anything other than the value that A1 contains.
Prob best to show your code or attach a spreadsheet.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Special-K, that's exactly what I'm saying, yes. The VBA codes doesn't recognize the result of the formula in A1, it only sees that there is a formula.
If I replace A1 with the text value of said content, the VBA code works properly.
Maybe something like Range("A1").Text .. not sure if that's a thing or not.
Range("A1").Value should return the result of the formula and not the formula. If it doesn't, there is something else going on in your code that's not apparent from your description. Can you post your code?
Hey, sorry about that, here's my code.
Basically on the worksheet Hours!, the dates are the columns across the top and the companies are the rows in column A. The amount of hours go in the table.
Please Login or Register to view this content.
If I change the dates from formulas to actual text, the code works. Otherwise, it returns the error "Could not find date"
Dates are funny things in Excel. They are numeric values that are formated to display as dates.
The quick answer is this...
Change the date format to the same date format as in row 1Please Login or Register to view this content.
Can you post an example of the formulas that are returning dates?
If posting code please use code tags, see here.
AlphaFrog I'm afraid that didn't work.
For the code were you suggesting this?:
Also, Norie, here's my formula: =Main!$D$42Please Login or Register to view this content.
That's what I was suggesting. Is MM-DD-YYYY the correct date format? I don't know what date format you are actually using.Originally Posted by her.rockstar
AlphaFrog I'm afraid that didn't work.
For the code were you suggesting this?:
Please Login or Register to view this content.
What happens if you try Application.Match instead of Find?
For example instead of this,
something like this.Please Login or Register to view this content.
Please Login or Register to view this content.
Norie it's saying Res is not defined. I'd be willing to try anything though!
Alpha, sorry about that. The result of the formula I had displaying as mm/dd/yyyy. I did try that with your suggestion and it made no difference. Sorry for the confusion.
Try adding this.
Please Login or Register to view this content.
Still could not find date
Try this...
Please Login or Register to view this content.
That worked! Like you widened the umbrella of what type of value it could search for right?
Thank you so much for doing this. I kind of borrowed this code from a different workbook I had - do you know if there's a simple chance I can make for a subtraction button?
You're welcome.
I don't know what you mean by a subtraction button. I have little understanding what you are attempting to do.
Also in the code, you don't set the variable dosubtract to anything so it is always False by default.
Sorry for the fogginess. I was attempting to build a test workbook for you, but you solved the problem too quickly!
Right now it's simply taking the number from C16 and adding it to the Hours worksheet in a specific cell. I'd like to have a button that would remove the amount in C16 from the specific cell, if possible. Not hugely necessary, though.
Last edited by her.rockstar; 06-07-2017 at 10:27 AM.
If you set the variable dosubtract = True that's exactly what the code will do.Sorry for the fogginess. I was attempting to build a test workbook for you, but you solved the problem too quickly!
Right now it's simply taking the number from C16 and adding it to the Hours worksheet in a specific cell. I'd like to have a button that would remove the amount in C16 from the specific cell, if possible. Not hugely necessary, though.
Alternatively, make the value in C16 negative to subtract.
That's great - thanks again for all your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks