Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How can I ..... Horizontal listing

Status
Not open for further replies.

jane2geo

Technical User
Oct 24, 2002
21
US
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
 




to be less rigerous, you could change the data type of vPct to Variant and see what happens.

BTW, I assumed, maybe falsly, that the first column of the query table had your % value. You need to adjust the column accordingly.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Changing the data type of vPct to Variant now works for column A.

Just don’t know the correct syntax for column 11 or “K” only,

For Each r In .Range(Cells(2, 1), Cells(.Rows.Count, 11)) returns 11 columns of data.
 




I am confused. Why are you looking at capturing values in TWO columns? Thot we were working on ONE column, containing PERCENTAGES???

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Still only one column K the eleventh column.

For Each r In .Range(Cells(2, 1), Cells(.Rows.Count, 1)) returns data column A only.

Looking for return data column K only, which is percentages.
 




then...
Code:
Each r In .Range(Cells(2, "K"), Cells(.Rows.Count, "K"))


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
That did it.
However, don’t know why but the data pairs now begin in column AO,
instead of the previous location AE. Ten additional columns to the right.?

The last hurdle is to post only data pairs, when the percentage value in column K changes.
Currently the data pairs are saved on every query update, even though the data has remained the same. Over time this will become an enormous worksheet, taking longer and longer to shift all the data.
 



Code:
    With [b].Cells(r.Row, 1)[/b].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


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
That brings the data pairs back, just outside the query range.

However, now new updates simply overwrite the old ones.
I’m not maintaining a historical record of changes.
 
New evidence.
The cell shift is happening, down range maybe ten columns or so.
Not at AE-AF where data pairs are posted.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top