Help with VBA code to use data called from first part of a macro in the second part

[suprsnipes]

Hi,

I am using a macro to create a formula that is placed in a couple of columns. I'm progressing ok but I need some help. What I want to know is I'm creating a request that is returning data which I want to use to perform the second part of my macro.

Is it possible after creating the request to somehow save the data that is returned for use during the second part of the macro? This is my code so far.

Sub YHOO_Buy()
Dim Id1 As Long
Dim Id2 As Long

req1 = "BUY_111_YHOO_STK_SMART_USD_MKT_{}_DAY_{}_{}_O_0_{}_1_{}_0_0_0_0_{}_0_0_{}_{}_{}_{}_{}_{}_{}_ISLAND'"

Id1 = Round((Date - 39000 + Time) * 1000000, 0)

ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!'id" & Id1 & "?place?" & req1
ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id1 & "?price" 

[COLOR=Red]'NOTE: The above line "edemo|ord!id" & Id1 & "?price" is the code for the request that returns a price which I want to use in the req2 code below (at this point it's marked "Price")[/COLOR]

req2 = "SELL_111_YHOO_STK_SMART_USD_LMT_" & "Price" & "_{}_DAY_{}_{}_O_0_{}_1_{}_0_0_0_0_{}_0_0_{}_{}_{}_{}_{}_{}_{}_ISLAND'"

Id2 = Round((Date - 39000 + Time) * 1000000, 0)

ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!'id" & Id2 & "?place?" & req2
ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id2 & "?price"

Any ideas or suggestions would be much appreciated.
suprsnipes

 

[suprsnipes]

Bump

 

[Ruddles]

Isn't the data you want to preserve in the worksheet at that point?

 

[suprsnipes]

After executing the macro the data is in the worksheet.

I want to use the data but I'm not sure how?

The macro will place the information in the next blank row. How can I use that information, i.e Price in column D, row X as it will be the next blank row available.

Is it best to read the data from the worksheet? I'm just not quite sure what to do.

 

[Ruddles]

When you do this, it places a couple of values one row below the last entry in columns A and D respectively:-

ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!'id" & Id1 & "?place?" & req1
ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id1 & "?price"

so now if you want to use those values, they are the last entries in columns A and D respectively, so they can be accessed with the following expressions:-

ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Value
ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Value

The other alternative is to save the two values in a couple of local variables whilst you have the opportunity:-

ValueA = "=edemo|ord!'id" & Id1 & "?place?" & req1
ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = ValueA
ValueD = "=edemo|ord!id" & Id1 & "?price" 
ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = ValueD

Then you can use ValueA and ValueD throughout the rest of your procedure. This is marginally faster than fetching them from the worksheet, I believe, but it won't be noticeable for just two values.

Does that help?

 

[suprsnipes]

Yes that helps heaps, thanks very much, it's taken me a while to work towards a solution, but this what I have come up with.

Sub YHOO_Buy()
Dim Id As Long

req = "BUY_111_YHOO_STK_SMART_USD_MKT_{}_DAY_{}_{}_O_0_{}_1_{}_0_0_0_0_{}_0_0_{}_{}_{}_{}_{}_{}_{}_ISLAND'"

Id = Round((Date - 39000 + Time) * 1000000, 0)

ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!'id" & Id & "?place?" & req
ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id & "?price"

End Sub

Sub YHOO_Sell()
Dim Id2 As Long

req2 = "SELL_111_YHOO_STK_SMART_USD_LMT_" & ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Value + 0.01 & "_{}_DAY_{}_{}_O_0_{}_1_{}_0_0_0_0_{}_0_0_{}_{}_{}_{}_{}_{}_{}_ISLAND'"

Id2 = Round((Date - 39000 + Time) * 1000000, 0)

ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!'id" & Id2 & "?place?" & req2
ThisWorkbook.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id2 & "?executed"
ThisWorkbook.Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id2 & "?price"
ThisWorkbook.Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id2 & "?status"
ThisWorkbook.Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id2 & "?filled"
ThisWorkbook.Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Offset(1).Value = "=edemo|ord!id" & Id2 & "?remaining"
ThisWorkbook.Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Id2

End Sub

Sub BuyRT()
    YHOO_Buy
    Application.OnTime Now + TimeValue("0:00:10"), "YHOO_Sell"
End Sub

 

[Ruddles]

Glad you've sorted it out. Thanks for the feedback.