Hey all,

I am new on the block trying to create a macro which creates 4 new
lines with 21 columns from a set of source data. The macro will need
to count how many rows are in the source data (as each row is a
seperate entity) and store that count in a variable, X. Then it
should create 4 new rows in a new sheet and populate each cell with
various bits of information. The cells will be filled with simple
vlookups and just links to the source sheet, or just plain numbers and
text hardcoded (no links).

I know this was vague, but can anyone at least tell me how to do some
simple things like create a vlookup in a target cell, or a way to
count the number of rows in the source data, store it in a variable,
and then use that to create the appropriate number of 4-row entries in
the resulting worksheet, there being 4 new entires per 1 row from the
source.

Im basically trying to generate journal entries based upon source
data. I am trying to teach myself VBA through internet websites, but
figured i would tap this forum for some knowledge!

Thanks alot, in advance.

-pogster

This should get you started.


Sub movedata()

Worksheets.Add after:=Sheets(Sheets.Count)
Set newsht = ActiveSheet

OldRowCount = 1
NewRowCount = 1
With Worksheets("Old sheet")
Do While .Range("A" & OldRowCount) < ""
DataA = .Range("A" & OldRowCount)
DataB = .Range("B" & OldRowCount)

With newsht
.Range("B" & (NewRowCount + 2)) = DataA
.Range("C" & (NewRowCount + 3)) = DataB

OldRowCount = OldRowCount + 4
End With
OldRowCount = OldRowCount + 1
Loop
End With

End Sub


" wrote:

Hey all,

I am new on the block trying to create a macro which creates 4 new
lines with 21 columns from a set of source data. The macro will need
to count how many rows are in the source data (as each row is a
seperate entity) and store that count in a variable, X. Then it
should create 4 new rows in a new sheet and populate each cell with
various bits of information. The cells will be filled with simple
vlookups and just links to the source sheet, or just plain numbers and
text hardcoded (no links).

I know this was vague, but can anyone at least tell me how to do some
simple things like create a vlookup in a target cell, or a way to
count the number of rows in the source data, store it in a variable,
and then use that to create the appropriate number of 4-row entries in
the resulting worksheet, there being 4 new entires per 1 row from the
source.

Im basically trying to generate journal entries based upon source
data. I am trying to teach myself VBA through internet websites, but
figured i would tap this forum for some knowledge!

Thanks alot, in advance.

-pogster

Thanks Joel,

I am having a little trouble understanding how the code works but only
because i dont know what the line:
"Do While .Range("A" & OldRowCount) < "" " says in english. Do
the following while the value of the range A(OLDROWCOUNT) is what?

My output is always the same, whatever is last in columns A and B,
gets moved to B3 and C4 respectively.

Gotta understand the code completely before i can modify it and use
it.

Thanks again Joel!

< - is not equal
the line is look for an empty cell - double quotes "" with noting between
them

VBA address cells using the Range statement like Range("A5"). I used the
counter OldRowCount to indicate the row number.

OldRowCount = 5
Range("A" & OldRowCount)

The two statement above is equivalent to
Range("A5")

" wrote:

Thanks Joel,

I am having a little trouble understanding how the code works but only
because i dont know what the line:
"Do While .Range("A" & OldRowCount) < "" " says in english. Do
the following while the value of the range A(OLDROWCOUNT) is what?

My output is always the same, whatever is last in columns A and B,
gets moved to B3 and C4 respectively.

Gotta understand the code completely before i can modify it and use
it.

Thanks again Joel!

Ah okay joel,

I am used to C++ and Java where != is not equal to. VBA newbie
indeed.

I now understand how it works. Thanks for the demonstration, it is
certainly a great building block.
Ill let you know how far i get before i get dizzy.

thanks.

Range function wants a string. If you are using C++ it make things more
difficult than using basic. You are going to have to do the combining. You
probably want to use the function cells(row,column) instead of range. It
will make programming in C++ easier.

" wrote:

Ah okay joel,

I am used to C++ and Java where != is not equal to. VBA newbie
indeed.

I now understand how it works. Thanks for the demonstration, it is
certainly a great building block.
Ill let you know how far i get before i get dizzy.

thanks.

One other pointer for somebody who doesn't know VBA BASIC. the "WITH"
statement is used to combine function that have a DOT infron of them


With Worksheets("Old sheet")
DataA = .Range("A" & OldRowCount)
end with

Is really
DataA = Worksheets("Old sheet").Range("A" & OldRowCount)

This feature of BASIC doesn't translate well to C++

" wrote:

Ah okay joel,

I am used to C++ and Java where != is not equal to. VBA newbie
indeed.

I now understand how it works. Thanks for the demonstration, it is
certainly a great building block.
Ill let you know how far i get before i get dizzy.

thanks.