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.
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...
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.
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.
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.
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...
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...
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...
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...
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...
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...
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.
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...
I'm using VB 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...
Not a solution, but work around.
I have been restarting the NAS server during periods of non use. Have not had the "disk or network" error since. Now my problem is automating this routine via Intrnet Explorer interface. Over night I boot all users off the backend then use second copy too make my...
Chaz
I too are using all the latest sp'ks and updates.
Win2k and Access2k, if it's a result of anything it's an update query I use for security logins. But yet it's happened on a Sunday am with only myself loged and mid-session. And still I can almost set my watch by this thing.
Ken
Thanks for...
Been looking for solution for six months.
My backend on shared NAS locks up every 160 to 200 hours. the "disk or network error"
No related posts indicate a relationship to time?
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.