How can I ..... Horizontal listing

[jane2geo]

I'm using VBA to track dynamic data in excel. This code
Works nicely but I want my list to fill horizontally not vertically. Can the new data be added from left to right?
Keeping the most recent in the left most part of the range.
Thank you in advance.


Private Sub Worksheet_Calculate()
Dim Over As Range
Dim msb As Integer
Application.EnableEvents = False
For Each Over In Range("$G$3")
If Over.Value > 0 Then
With ThisWorkbook.Names("list1").RefersToRange.CurrentRegion
With .Offset(.Rows.Count, 0).Resize(1, 1)
.Value = Now
.Offset(0, 1).Value = Over.Value
End With
End With
End If
Application.EnableEvents = True
Next
End Sub

 

[SkipVought]

Hi,

This is a STOPPER!

For Each Over In Range("$G$3")
'...

you have defined ONE CELL, G3, in the range of your loop.

What did you INTEND the range to be?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

That is my range. I also have a cell =A3 to trigger the event. The field is updated via a web query. This is my adaptation of the code at

http://wxw.exceluser.com/solutions/timetrack.htm

Crude possibly but it works, just need to see the results
Horizontally. I would be grateful for any advise.

 

[SkipVought]

Your link does not work.

For Each Over In Range("$G$3") does ONE ITERATION, which does not make sense, as Over has one and only one reference, G3. So WHY the loop?

Now, your question, "Can the new data be added from left to right?"

The answer is, YES!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

Ok Skip. I’m a beginner I have never used VBA in excel before. As a novice I search for solutions for my needs on at a time. I found code at this site,

http://www.exceluser.com/solutions/timetrack.htm,

that only updates on a manual change. Then I found wssheet_onCalc code that updated with my web query. I guess that code was for a range and I modified it for one cell testing. Now after ten days of surfing for the left to right data list I have come here,to a forum that I belonged to for years to get some help. It sounds like you can help, so here are my needs in a nut shell. I want to maintain recent history of data changes to a column of percentages that update via web query G3. To the right of G3 I need the time and value of the lastupdate. Two cells to the right of that I want the second last time and value, etc. See below.
G3
66% 9:47 0.66 9:39 0.55 9:28 0.44
44%

29%
71%

45%
55%

Thank you for your time. George

 

[SkipVought]

    With [G3]
'shift the adjacent data to the right
        .Offset(0, 1).Insert shift:=xlToRight
        With .Offset(0, 1)
'insert the TIME only (if it were me, I'd store date & time
            .Value = Now - Date
'format the cell to display the time as hrs & mins
            .NumberFormat = "hh:mm"
        End With
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

Thank you, I was flirting with that “shift:=xlToRight” just couldn’t get mine to work. However, your sample is only recording the time. I want to keep the history of the G3 value as well, but only when it changes. Column G will have many percentage values, some changing and some not. Additionally, I need to start the list several
spaces to the right because of the query update range.

G3 space space space time value time value time value
66% 9:47 0.66 9:39 0.55 9:28 0.44
44%

29% 9:32 0.29 9:30 0.33
71%

45% “ “ “ “
55%

I appreciate your help, George

 

[SkipVought]

Sub test()
    Dim r As Range, i As Integer, vPct As Single
    
    For Each r In Range(Cells(3, "G"), Cells(Cells.Rows.Count, "G").End(xlUp))
        With r
            If Trim(r.Value) <> "" Then
                vPct = .Value
        'shift the adjacent data to the right
                Range(.Offset(0, 1), .Offset(0, 2)).Insert shift:=xlToRight
                For i = 2 To 3
                    With .Offset(0, i)
                        Select Case i
                            Case 2
                                .Value = Now - Date
                                .NumberFormat = "hh:mm"
                            Case 3
                                .Value = vPct
                                .NumberFormat = "0%"
                        End Select
                    End With
                Next
            End If
        End With
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

Wow, now you’re over my head. Not sure how I warrant your attention but thanks.
Skip I don’t know which event to place the code into. Simply as sub test I had no results.
In the work sheet calculate event, the code runs and flickers about 256 times then presto the time and value appears. However, on the second update another 256 flickers and the RT error ‘1004’ “ms can’t shift a non blank cell off the worksheet”. I will
persist to test this code because it’s so close. I maintain to believe it must go in the worksheet calculate event, as this is the only one I can get to auto update with a web
query. I find the rem’d explanations very helpful. Thanks again. George

 

[SkipVought]

http://msdn.microsoft.com/en-us/library/aa221531(office.11).aspx

http://msdn.microsoft.com/en-us/library/aa221596(office.11).aspx

I'd recommend using the Query AfterRefresh Event. Definitely not the Calculate Event.

"ms can't shift a non blank cell off the worksheet"

Surely you do not have 256 columns of data???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

Had success creating and using a Query AfterRefresh Event.
Thanks Master Miyagi, “wax on… wax off”.

The code seems to work on the first update but with a type mismatch at vPct = .Value. Upon stopping the debugger, time and value are inserted skipping one column , other cells are shifted to right. I have 22 columns of data,
with G being the eleventh, therefore, I need to skip 12 columns before inserting time and value and shifting. On the second update and code start the columns shift back to the original position and the target cell “I” is now formatted as time data, originally being another percent column.


Additionally the code runs when the value remains the same. “ If Trim(r.Value) <> "" Then ” Should we have an elseif doing nothing?

Regards George

 

[SkipVought]

"type mismatch at vPct = .Value"

Using your Watch WIndow, what value is in r.Value, as it SHOULD be a "percentage".

"I have 22 columns of data, with G being the eleventh,"

Huh?

"On the second update and code start the columns shift back to the original position and the target cell "I" is now formatted as time data, originally being another percent column."

Hmmm? I run multiple times and I is ALWAYS Time.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

Sorry, moved my test cell to K and M being the cell where the list starts.

The r.value is percent. But prior to the code running M is an additional unused percent column. After code execution M or [your "I"] is always time.

The key is to get the list to start outside the query range, twelve columns. Also can we not execute if r.value = no change in value?

 

[SkipVought]

So let me understand.

You want the data pairs to start immediately to the RIGHT of the QueryTable?

Sub TestWithQueryTable()
    Dim r As Range, i As Integer, vPct As Single
    
    With Sheets("Sheet1").QueryTables(1).ResultRange
        For Each r In .Range(Cells(2, 1), Cells(.Rows.Count, 1))
            If Trim(r.Value) <> "" Then
                vPct = r.Value
        'shift the adjacent data to the right
                Range(r.Offset(0, .Columns.Count), r.Offset(0, .Columns.Count + 1)).Insert shift:=xlToRight
                For i = 0 To 1
                    With r.Offset(0, .Columns.Count + i)
                        Select Case i
                            Case 0
                                .Value = Now - Date
                                .NumberFormat = "hh:mm"
                            Case 1
                                .Value = vPct
                                .NumberFormat = "0%"
                        End Select
                    End With
                Next
            End If
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, Be sure to use YOUR SHEET NAME rather than Sheet1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

The revised data pair location is perfect.
Apparently the query range was larger than I thought.
Time stamp [cell AE] is good.
But still getting that "type mismatch on vPct = .Value"
After resetting the macro to run, and re-query, the second
Data pair is filled flawlessly as well.

Not sure how to get Watch Window to read r.Value. This is cell watch only?

The cell your code s capturing now eludes me.
[For Each r In .Range(Cells(2, 1), Cells(.Rows.Count, 1))
I’m trying to capture K3.
Value of K3 is 44% , data log [cell AF] is 3972800%
I tried a second data set query,
Where the value of K3= 51% and [cell AF] registers 3972700%

Could the code be adjusted to execute on all percent values in column K ?
Column K is either null or contains a %value.

 

[SkipVought]

Put a BREAK in your code.

faq707-4594



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

wax on .... wax off"

Watch : : vPct = r.Value : False : Variant/Boolean : Class1.qt_AfterRefresh

 

[SkipVought]

now you can program to trap that too.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jane2geo]

For Each r In .Range(Cells(2, 1), Cells(.Rows.Count, 1)), r.value= 20:00 EST

For Each r In Range(Cells(3, "K"), Cells(Cells.Rows.Count, "K").End(xlUp)), r.value= 0.52

Both stop code, type mismatch.