[RESOLVED] Userform data not working with vlookup in cell

[DannySpring]

Hey all,

Long time lurker, first time poster (questioner?)...

I have been working with code for about a year, but still don't really know much about it. I can take what I find and modify it to my needs.

I have a problem that I cannot find an answer for:

I am using a Userform to get data from a user that gets added to several places on an active row via ActiveCell.Offset. One of the pieces of data gets dropped into column J using this method and in column K I have a VLOOKUP formula that does not work when the data is just dropped in as ActiveCell.Offset(0, 9).Value = Me.txtStore.

So, the data gets from the Userform into the cell just fine and if I type the exact same information into the cell the VLOOKUP works fine. In fact, if I click into the Formula Bar and hit enter without changing anything, the VLOOKUP starts to work. I have tried formatting the cell as General and Number since the lookup is a number range, but without success.

Further across the page the simple math formula works fine, so I am sure that it is actually a real value, but the VLOOKUP is not seeing it for what it is.

Hope this makes sense.

I would appreciate anything you experts can contribute to my dilemma and want to thank you for all your other posts and replies that I have used in the past. I would be lost without you.

D.

[westconn1]

you could try activesheet.calculate
do you have calculations set to manual?
do you have screenupdating off?

if that does not help
can you post a sample workbook, with userform that demonstrates the problem

[Ecniv]

Could it just be that the data you are using is being confused from string to number (assuming you are entering a number) ?
Vlookup is picky with numbers, its best to force both bits to text for comparison.

If the data is a string, well then its how you are putting the data from the form into the cell, but i'd find that unlikely...

[DannySpring]

I tried to upload the file, but it keeps saying it is an Invalid File.

I tried the ActiveSheet.Calculate, but no change.
It is still set to auto calculate, and the other formulas are working right away as well.
The macro turns off screen updating at the beginning and then restarts it at the end.

Like I said, everything works great and the other 5 pieces of information from the UserForm goes into the sheet fine.

I will try changing both the value and vlookup value are text and see if that changes anything, otherwise I am thinking that because the cell can also get data from a Data Validation list, it is not working. I am going to try a work around.

Thank you Ecniv and westconn1

D.

[vbfbryce]

You need to zip the file first to attach it.

[DannySpring]

if that does not help
can you post a sample workbook, with userform that demonstrates the problem

I have attached the file. To run the SubForm, there is a hidden command button at E1 where it says "ID". I have the form auto size adjust the columns so everything always fits so it constantly locks on you. I have a command button at K1 where it says "Site Name & Address" to unlock it. There is no password anyways. This is why I had to add the multiple lines of unlocking during the UserForm VBA because it kept accessing the resizing macro in "This Workbook". I have not looked into disabling it during the Userform macro.

I tried changing the value and lookup array to text to see if that would help, but it didn't. VLOOKUP completely stopped working.

Anything you can do to help get it to work. I am hoping that you don't tell me I can't have Data Validation in the same cell, that would suck. LOL

D.

[vbfbryce]

As Ecniv said in #3, I think you have a number vs string issue. It looks like you have strings in some places on the Store Data sheet and numbers in other places. Then you are trying to change the newly entered info to a number, but I think you might be one column off with your offsets. I would recommend naming the columns whose values or formats you're changing rather than using the offset.

[DannySpring]

Ok, I will try and fix the data. I thought they were all numbers. I had not thought about naming the columns, that is an excellent idea.

Thank you for your help!

D.

[Paulrspencer]

I have the same problem with the vlookup not recognising the format on a string (Months January to December) although I have tried both Text and General.
The Vlookup defaults if I use a cell reference or a variable, but works if I replace the string with text'
Vlookup(Mday,Mmonth,Myear) won't accept the month variable. Vlookup(J2,J3,J4) won't accept the J3 cell reference.
Vlookup (Mday,September,Myear) works fine but isn't helpful when you have to change manually.
In the end I had to create Case statements using the months to create and write the vlookup formulae to the spreadsheet, which works well.
I have been using VB since the 1980's and never had a problem in the past. What have they done to this function?

[westconn1]

you could try

Vlookup(J2,cstr(J3),J4)

[Paulrspencer]

Thanks Pete, I resolved the situation, using the case statements, which isn't a perfect way of programming. But if I create a similar program I will keep that context in mind, many thanks.