-
Mar 27th, 2018, 12:26 PM
#1
Thread Starter
New Member
Record cell values into columns - Excel
Hello!
English isn't my first language so I hope you will understand me
I need help from Excel pros
I have a workbook with two sheets ( datafeed and record )
On "datafeed", column B, cells B2 to B300 I have live prices from internet
Currently I use the following :
Sub my_onTime()
Application.OnTime Now + TimeValue("00:00:01"), "my_Procedure"
End Sub
Sub my_Procedure()
With Sheets("record")
rw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Range(.Cells(rw, 1), .Cells(rw, 2)).Value = Sheets("datafeed").Range("a3:b3").Value
End With
ThisWorkbook.RefreshAll
my_onTime
End Sub
which records prices from"datafeed" to rows in "record", but that isnt what i want.
My wish is :
that Excel would record price changes to"record" Sheet into columns not rows.
"Graphic" display what I want:
Sheet1 - "datafeed" ... B1 = 155............Sheet 2 - "record".......cell B6 = 155.....cell C6 = 150.....cell D6 - 145.....cell E6 - 140,....
Sheet1 - "datafeed" ... C1 = 66.............Sheet 2 - "record".......cell B6 = 66.....cell C6 = 67.....cell D6 - 66.....cell E6 - 65,....
Also last recorded price must be put in first column Sheet 2 - "record" - in to cells: C6, D6, E6,......
(Right now last recorded value is put in last row )
Any help will be greatly appreciated.
Thank you!
-
Mar 27th, 2018, 03:41 PM
#2
Re: Record cell values into columns - Excel
post a workbook with some sample data (zip first)
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Mar 27th, 2018, 05:37 PM
#3
Thread Starter
New Member
Re: Record cell values into columns - Excel
-
Mar 28th, 2018, 03:19 AM
#4
Re: Record cell values into columns - Excel
you should attach all to your post here, rather than external sites
you can test if this does what you want
Code:
Dim r As Worksheet, df As Worksheet, cel As Range, fnd As Range
Set r = Sheets("record")
Set df = Sheets("datafeed")
With r
For Each cel In df.Cells(2, 1).Resize(df.Cells(Rows.Count, 1).End(xlUp).Row - 1)
Set fnd = .Range("d:d").Find(cel)
If fnd Is Nothing Then
Set fnd = .Cells(Rows.Count, 4).End(xlUp).Offset(1)
fnd.Value = cel
Else
fnd.Offset(, 1).Insert xlToRight
End If
fnd.Offset(, 1) = cel.Offset(, 1)
Next
End With
it appears to work correctly, if i understood right, and runs without error,
any codes that are not found are added at the bottom of the column
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Mar 28th, 2018, 06:24 AM
#5
Thread Starter
New Member
-
Mar 28th, 2018, 03:39 PM
#6
Re: Record cell values into columns - Excel
Also, can you please rewrite the code, so the data on Record sheet would start with column W instead of D?
Set fnd = .Range("d:d").Find(cel)
change this line to W:W
i am not really sure what is happening with the formula, or what you want result you want
which columns do you want to compare?
do you want to compare the same columns by number or the relocated columns after updating the worksheet?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|