Extracting Values from VBA Code to Store

[canada01]

Hello,

I'm trying to find a way to pull the value of an item in my VBA code to get stored each time a loop runs if certain conditions are met.
I have established that ShiftTime = "" at the beginning of the loop and then a value is calculated at the end of the loop, before moving on to the next set of data and getting cleared again.

I would like to know how I can have my code take that value each time the loop runs and put the value into another sheet or workbook. Everything I've found in my research points to taking existing data (already in an Excel sheet) and writing a code to copy and paste some values to a new sheet.

Any help is tremendously appreciated by this new user!

Below is a filtered-down version of my macro:

Sub Analysis()​

​

Dim SessionRng As Range, Dn As Range, n As Long 'Declaring ranges​

Set SessionRng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)) 'Assigning range values​

​

With CreateObject("scripting.dictionary") 'Creating a dictionary for the array​

.CompareMode = vbTextCompare 'Defining Compar Mode as text compare in the range​

​

For Each Dn In SessionRng 'This first loop will create a unique range relating to each unique value in column A​

​

If Not .Exists(Dn.Value) Then 'If the value of the cell doesn't exist in the dictionary yet...​

.Add Dn.Value, Dn 'Create a new entry with the value of the active cell​

Else 'If it does exist...​

Set .Item(Dn.Value) = Union(.Item(Dn.Value), Dn) 'Add this row to the entry already in the array​

End If​

​

Next​

​

'Declaring k as key entries and p as rows​

Dim k As Variant, p As Variant​

​

'I declare a bunch of variants here​

​

'This loops through each session​

For Each k In .keys​

​

'I name a bunch of items here included ShiftTime, IGTime, and SFPTime​

​

'This loops through each row within each session​

For Each p In .Item(k)​

​

'I have a bunch of conditional statements here that run on each row in the session
'Values are assigned to the items I created, including IGTime and SFPTime, but are all cleared at the start of the next session​

​

Next p ' Next row​

​

'There's a bunch of math done down here, including finding ShiftTime if the conditions for SFPTime and IGTime are met in that session.

​

If SFPTime <> "" And IGTime <> "" Then​

ShiftTime = SFPTime - IGTime​

End If​

​

Next k ' Next Session​

​

End With​

​

End Sub​

 

[Ruddles]

If you want to capture the value of x and save it in Sheet2, this code will do that:-

 With ThisWorkbook.Sheets("[COLOR=#FF0000][B]Sheet2[/B][/COLOR]")
   .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, "A") = [B][COLOR=#FF0000]x[/COLOR][/B][COLOR=#FF0000][/COLOR]
End With

You'll need to replace x and Sheet2 with the names of the variable you want to save and the sheet you want it saved in.

And you'll need to wrap it in an If ... Then ... EndIf block if you only want it captured under specific conditions.

Does that help at all?

 

[canada01]

Hi Ruddles,

Thanks for the advice--it works perfectly! I understand what it's doing, too, which is even more helpful than the working code.

Thanks again! I really appreciate the assistance.

 

[Ruddles]

You could even track it through time:-

  With ThisWorkbook.Sheets("[COLOR=#ff0000][B]Sheet2[/B][/COLOR]")
[B][COLOR=#ff0000]   [/COLOR][/B][COLOR=#ff0000][/COLOR][COLOR=#000000] .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, "A") = Format(Now(),"dd-mmm-yyyy hh:mm:ss")[/COLOR]
    .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row + 1, "B") = [B][COLOR=#ff0000]x
[/COLOR][/B]  End With