MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Looped VBA to paste special values

  Asked By: Juana    Date: Aug 31    Category: MS Office    Views: 3546

I'm fairly green
when it comes to creating VBA and I'm working on a special project
that will most likely need to be automated somehow. I am attempting
to create a VBA macro that does 3 things:

1- Based on a worksheet cell range, step through each cell to the
first non-zero value. There should be a maximum of 1 non-zero value
per row.
2- Take active non-zero cell contents and copy/paste special value
the contents X number of times to the right. The number of times
pasted is stipulated by the numeric cell contents in column A of same
3- Loop, move to next row. Select next non-zero cell in range until
finished. Repeat copy/past special value.

Any ideas?

This is what I have thus far (very rudimentary). Any help/insights
would be greatly appreciated!

Do While ActiveCell.Value > 0
Call moveNext

End Sub
Sub moveNext()
Dim i As Integer
Dim counter As Integer
Dim freq As Number

counter = 0

Selection.Copy Destination:=ActiveCell.Offest(0, Range).Select



5 Answers Found

Answer #1    Answered By: Dallas Martin     Answered On: Aug 31

I have been doing some more tinkering with my attempted macro.
Although I may be a bit closer, I still need some help... Anyone have
any insights? I'd really appreciate it! Thank you.

Range("A20:AL27").Select 'Update later with actual range
Do While ActiveCell.Value > 0
Call moveNext 'Calls sub-routine

End Sub
Sub moveNext()

macro  to copy/paste special  selected cell  X nubers of time. Then
move to next line item.
Dim i As Integer
Dim counter As Integer
Dim freq As Integer

Range(0, 1).Select = freq 'Trying to assing the contents  in
column A as the # of times to copy/past. This does not work

counter = 0

Do While counter <= freq
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Select
counter = counter + 1
ActiveCell.Offset(1, 1).Select
End Sub

Answer #2    Answered By: Tracy Cole     Answered On: Aug 31

What help do you need??????????????????

Answer #3    Answered By: Adalrich Fischer     Answered On: Aug 31

I specifically need someone to take a look at the attached VBA lines to
help me identify why my attempts are not working. As noted in my
initial post, I'm still very green when it comes to VBA and I'm at a
loss on how to proceed next. I have been trying to get things  to work
out on my own, but I need a bit of expert insight/help to help finalize
my plan.

Right now, the code (see below) errors out when I am attempting to
assign the contents  of the current row, column  A to the variable "freq."
I would like to make this a relative reference, as the plan is to loop
this assignment process. Contents in column A represent the number  of
times I'd like to copy and paste  the first non-zero number in the row.

Range(0, 1).Select=freq

Maybe an example would be helpful. Below is a sample of the spreadsheet
that I would like to manipulate with the macro:

F Jan-2007 Feb-2007 Mar-2007 Apr-2007
May-2007 Jun-2007 Jul-2007 Aug-2007
12 0 0 0 0 $ 50 0

12 0 0 0 0 0 $ 100

12 0 0 0 $ 75 0 0

Here is what I'm attempting to do. My spreadsheet will have the months
moving over to the right for the next few years, so this example
represents a sub-set of the actual application. The first column (in
gray background), represents the total number of months I'd like to
carry the first non-zero cell  contents. For the first row, I'd like the
VBA to start at Jan-2007 and do a validation process. If the contents
are zero, move  right to the next cell (same row). This continues until
we reach May-2007. I'd like to copy/paste special  value the $50 eleven
more times (a total of 12 times, minus the original amount in May).
Once this is completed, I'd like the macro  to step to the next row  and
repeat the process.

I have made my best attempt at getting the shell code put together, but
I'm rapidly moving well above my skill level. I was hoping that someone
could step in and help fill-in the blanks.

Answer #4    Answered By: Volker Fischer     Answered On: Aug 31

The particular issue is that to assign a value to a variable you must put the
variable on the left of the equal sign and the value on the right.

You have

Range(0, 1).Select = freq

This is trying to assign the value of freq to "Range(0, 1).Select"

If you want to assign to freq you need something like

freq= activecell.value

But, I think you have other problems.

Answer #5    Answered By: Marshall Bryant     Answered On: Aug 31

This statement

> Range(0, 1).Select=freq

has several problems.

Range has two forms. The one you want takes a string parameter that
identifies a range. E.g. "A1".

There is also a Cells function that takes a row  number (>= 1), and a column
number or letter, to locate a specific cell.

However, neither of them has the concept "current row".

The active cell  is ActiveCell and you can ask it for its row:

Therefore A of the active  row is Cells(ActiveCell.Row, "A") and its value is
Cells(ActiveCell.Row, "A").Value.


> Range(0, 1).Select=freq

is trying to put the value in freq into something. Assignment is towards
the left. So

freq = Cells(ActiveCell.Row, "A").Value

I notice that you have used "Select" in your statement. Do you really want
to do this? (Doing a select  will move  the active cell position to that
location.) If all you want is the value of the cell, don't do a select.

Didn't find what you were looking for? Find more on Looped VBA to paste special values Or get search suggestion and latest updates.