Previous Topic Next Topic

What Type does it want? (VBA question)

Solved/Closed
InsertNicknameHere - Dec 3, 2014 at 03:59 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Dec 11, 2014 at 11:13 AM
Hello,

So I have a pull-down list in Excel 2003 that is an expanded version of this:
011
012
01T
111

If I type in the cell (rather than using the pulldown) it says 111 (integer) is a type mismatch, but '111 (leading apostrophe makes it a string) works.

So it wants a string. Fine.

Problem is in VBA code ALL of the below says, 'type mismatch'.

Cells("D" & ThisRow) = "01T" ' Doesn't like strings!
Cells("D" & ThisRow) = "111" ' or this string
Cells("D" & ThisRow) = CStr("111") ' or this string
Cells("D" & ThisRow) = "'111" ' With leading apostrophe, to simulate my typing.
Cells("D" & ThisRow) = 111 ' Doesn't like integers!

So if it doesn't like strings or integers...what the heck type is it looking for?

I want to use strings because I need 012 not 12, and 01T has to be a string in any case.

Any ideas gurus?

Thanks,
Mark


2 replies

Answer 1 / 2
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 8, 2014 at 11:46 AM
Hi Mark,

It's unclear to me what you are trying to do.

I thought you wanted to use VBA to input a value into a cell which has drop down list on it.

So I created a drop down list with your sample data and then used VBA to put data in the drop down list cell. No errors popped up.

Am I understanding you correctly?

Best regards,
Trowa
0
Answer 2 / 2
ThatGuyAskingAQuestion
Dec 9, 2014 at 03:47 PM
OMG....Cells is Row, column I think, and I was doing column, row....I bet that is my problem.

Still fairly new to this, it baffles me that some methods want row, column and some want column, row. You'd think they could make up their mind....

If that's not it, then I don't know what we are doing differently from each other... I am back on QTP 11 if that matters.

Thanks,
Mark
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Dec 11, 2014 at 11:13 AM
You are right ThatGuyAskingAQuestion, I have overlooked that.

Range works like:
Range("A1") or Range("A1:B10")

and Cells works like:
Cells(1,1) or Cells(1,"A")

With Range you can refer to both a range as well as a single cell, while Cells can only refer to a single cell.

With Range you can use predetermined row and with Cells you can use both predetermined row and column.

So Range could be:
Range("A" & LastRow)

And Cells could be:
Cells(1,LastColumn) or Cells(LastRow,LastColumn)

And then you have the ability to combine the two, which could be:
Range(Cells(1,"A"),Cells(LastRow,LastColumn))

I guess it comes down to "getting used to it".

Hopefully this clarifies a few things.

Best regards,
Trowa
0