I am pulling financial data via a SQL query into Excel and have problems using V Lookup with one column of data returned so need to re-format the cells in this column.
I have 2 macros.
The first one refreshes the data by updating the Command Text and runs the Stored Procedure in SQL with variables to return the data.
The second selects the 'offending' column and re-formats the cells using the 'Text To Columns' function to allow the V Lookup to work.
If I run these separately they work fine however I want to run from one button both the data refresh and then re-format macro.
A strange thing happens when I do this.
Both run, the data is returned and I can see the V Lookup values appear and then almost immediately they all return to #N/A. I have tried adding a delay but this makes no difference. I can see that the second macro is running as it ends up in the cell I specify.
Help would be appreciated. Thanks.
'Set the Query Command Text and refresh the data for UK
.CommandText = "EXEC [dbo].[SP$$XXXX TRIAL BALANCE UK] '" & Range("D1").Value & "'" & "," & "'" & Range("D2").Value & "'"
Application.Wait (Now + #12:00:10 AM#) ' Wait for 10 seconds
'Select all cells with a value
Selection.TextToColumns Destination:=Range("D5"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuo
e, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True