PDA

View Full Version : Create a macro to add data progressively



grego
15-08-2010, 08:23 PM
Hi I just discovered macros about 48 hrs ago:horrified
Any way I am trying to create a trade journal where I can enter data into
a specific sheet and then click a button to transfer the data to another sheet that records the info progressively down the page.

I managed to do a simple one, however it just writes over the data I already have. In other words it just rewrites to the same row each time.
Then I tried to work out that if i used "Relative References" I thought it might progressively work down the sheet recording my data.
i think I'm on the right track but just don't have the knowledge to record all the steps to get it to work.
Please help?
Thank You
Greg:thanks

pctek
16-08-2010, 07:39 AM
So when you started to record, you copied the data, then clicked to open a new sheet then pasted? It should record all those steps.

grego
16-08-2010, 11:04 AM
Hi
Yes I had already created the sheet I want it pasted into along with appropriate headings.

I have set up the headings,in the same order on both sheets, So When I copy the data from one sheet and paste it into the other sheet
it pastes along the row and the data lines up.

What I want to be able to do is use a form (in say sheet "A") repeatedly where I fill in the data after each share trade, click a button which then updates
"sheet B" on the next available row.

Presently my macro just overwrites the same row on "sheet B" each time I reuse the (macro) form from "Sheet a"

ps. I have already created a simple macro to clear the form in "Sheet A" after each use and know how to assign a macro to an object
:thanks Greg

fred_fish
16-08-2010, 12:33 PM
'set start values
rowpointer = 0
colpointer= 1
cellval = "BLANK"

'get blank line
Do Until celval = ""
rowpointer = rowpointer + 1
cellval = Sheets("Data").Cells(rowpointer, colpointer).Value
Loop

' YOUR CODE HERE

This is a quick hack I use to loop down a sheet in one column looking for a blank cell.
When it finds one and exits the loop, you can use the 'rowpointer' variable in your 'Insert Data' code.

grego
16-08-2010, 01:45 PM
Hi Fred Fish

Thanks for your help. below is the full code (yours and mine)
I replaced your line
"cellval = Sheets("data").Cells(rowpointer, colpointer).Value

with

cellval = Sheets("Sheet1").Cells(rowpointer, colpointer).Value

"Sheet 1 being the sheet that I want the data loaded into

Would that be right? or are there other reference changes in the code required as well?

Also Would you mind taking a look at my part of the code, Because when I combine your code and mine it still doesn't work. So i am assuming i have done something incorrect when recording
Thank you
Greg





' Macro10 Macro
'

''set start values
rowpointer = 0
colpointer = 1
cellval = "BLANK"

'get blank line
Do Until celval = ""
rowpointer = rowpointer + 1
cellval = Sheets("Sheet1").Cells(rowpointer, colpointer).Value
Loop

Range("B20:J20").Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A4").Select
ActiveCell.Select
Sheets("Visual Joural Entries").Select
Application.CutCopyMode = False
ActiveCell.Select
Sheets("Sheet1").Select
End Sub

fred_fish
16-08-2010, 02:00 PM
Hi Fred Fish

Thanks for your help. below is the full code (yours and mine)
I replaced your line
"cellval = Sheets("data").Cells(rowpointer, colpointer).Value

with

cellval = Sheets("Sheet1").Cells(rowpointer, colpointer).Value

"Sheet 1 being the sheet that I want the data loaded into

Would that be right? or are there other reference changes in the code required as well?

Also Would you mind taking a look at my part of the code, Because when I combine your code and mine it still doesn't work. So i am assuming i have done something incorrect when recording
Thank you
Greg





' Macro10 Macro
'

'set start values
rowpointer = 0
colpointer = 1
cellval = "BLANK"

Range("B20:J20").Select
Selection.Copy
Sheets("Sheet1").Select

'get blank line
Do Until cellval = ""
rowpointer = rowpointer + 1
cellval = Sheets("Sheet1").Cells(rowpointer, colpointer).Value
Loop

Cells(rowpointer, colpointer).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A4").Select
ActiveCell.Select
Sheets("Visual Joural Entries").Select
Application.CutCopyMode = False
ActiveCell.Select
Sheets("Sheet1").Select
End Sub
Try that.
Fixed typo in cellval variable & added line to select blank target cell before pasting.
NOTE: This code is from a XL2k macro, may be some differences in your version.
Also if you open the VB editor you can step through the code with the F8 key, so if it is failing you can figure out where, and also watch what each line does. See also "Add watch" on the Debug Menu to watch variable values.

grego
16-08-2010, 02:13 PM
Also if you open the VB editor you can step through the code with the F8 key, so if it is failing you can figure out where, and also watch what each line does.
I hit the f8 key after loading it into the editor and the following line came up with a "syntax error"message


'get blank line
Do Until *cell*val = ""

Regards
Greg

fred_fish
16-08-2010, 02:37 PM
I hit the f8 key after loading it into the editor and the following line came up with a "syntax error"message


'get blank line
Do Until *cell*val = ""

Regards
Greg

:)
Looks like your copy/paste from the forum picked up the bold tags around my typo correction.
take the '*' out of the variable name.

grego
16-08-2010, 02:52 PM
Thank you Fred Fish

that fixed the problem and it now does what i needed

Your help is greatly appreciated :thanks:thanks:thanks:clap