Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

What Type does it want? (VBA question) [Solved/Closed]

Report
-
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 -
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
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 459
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
Answer 2 / 2

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
Report
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
 459
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!