Posts by rory

The code does not convert any data. Your code doesn't work in 2016 either because many of the 'number' values in the source Raw Data sheet are actually text.

Nothing in that code differs between Excel versions.

I know, but you asked for a formula, so...

The data in your Raw Data sheet is stored as text. I wasn't talking about your image.

FWIW you can also use:

=TEXT(D10,"0000-00-00")+0

It's nothing to do with the version - those are stored as text, not numbers.

You might want to just amend that line anyway to avoid the debug error - something like:

If n > 0 then
Sheet2.Cells(2, 1).Resize(n, 5).Value = var
else
msgbox "No matching data. Please make sure the dates in the data are not text!"
End If

Those are still text.

The dates in your data sheet are actually text, so there is no match for the search criteria and n = 0. Since you can't resize a range to 0 rows high, an error occurs. You can avoid the error by adding If n > 0 then to the start of that line, but that will simply mask your problem. You need to fix the source data so that it has actual dates.

The pivot table has a Tablerange1 property which is the body of the pivot table. You can use that to figure out which rows to copy. But your current code should never select all the rows in the sheet.

What kind of app are you talking about?

Glad we could help.

There is no row 0 in Excel, so this approach will not work for that currently. You could add 1 to both x(1) and x(2) and subtract it in the evaluation:

LastR.Resize(val(x(2)) - val(x(1)) + 1) = Evaluate("""" & pref & """&row(" & x(1) + 1 & ":" & x(2) + 1 & ")-1")

No, you can't do that with a stock chart, I'm afraid. It might be possible to do it using an XY scatter instead with lots of dummy series and custom formatting but it would take quite a bit of work I suspect.

You cannot set the font colour from a UDF used in a cell.

Your function doesn't need that sub. As I said earlier, you have already extracted the data from the server into a recordset. It's not clear what you're trying to do with the returned data, but I suspect you should be adding a WHERE clause to your SQL to restrict the data returned based on the function parameters.

I still have no idea what you mean. Your function already creates and populates the recordset. What does the sub have to do with anything?

Sorry, but I am not following. Your code (from post 3 which is the only place you have those lines) makes no sense to me since you populate the recordset with data from the CSV and then immediately destroy and recreate the recordset with a second Set rs = New ADODB.Recordset line. Why do you do that?

There doesn't seem to be a question in there anywhere. What exactly is your problem currently?

You can use SQL on a text file. I don't understand why the OP opens a recordset, populates it, then replaces it with a new blank recordset, and finally tries to use a recordset as a worksheet.

This thread is 10 years old. I suggest you start your own thread if you have a problem to solve.