MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Macro help newbie: VB conditional if statement to copy paste cell

  Asked By: Cesar    Date: Jan 31    Category: MS Office    Views: 21383

I am trying to use VB to write a macro which cuts data from a range of
cells which I have named. I then want use an if statement to determine
where in the spreadsheet the copied data is to be pasted.

e.g. sample spreadsheet
Column A B C D
1 2001
3 1
4 2

Cell B1 is named "Year"
Cells B1:B2 is named "Data"
Cells C1:C2 is named "Out1"
Cells D1:D2 is named "Out2"
In this example "year" is equal to 2001,
I want to write a VB macro which copies "Data" and pastespecial values
to "Out1" if "year" equals 2001 otherwise if "Year" is equal to 2002,
macro should copy "Data" and pastespecial values to "Out2"



6 Answers Found

Answer #1    Answered By: Devlan Jones     Answered On: Jan 31

This should do it:

Sub MoveToYear()

If Range("Year").Value = 2001 Then
Range("Out1").PasteSpecial Paste:=xlPasteValues
ElseIf Range("Year").Value = 2002 Then
Range("Out2").PasteSpecial Paste:=xlPasteValues
End If

End Sub

Btw, with more than 2 years in play, a Select Case construction might be
more elegant than Ifs. With more than 10, I'd be looking to make a
string from the year value and converting it into its corresponding
range name.

Answer #2    Answered By: Heru Chalthoum     Answered On: Jan 31

I have a
question for you though, you used ranges specified by names ("year", "data",
etc)...is that how you would define a range  if the range was named?

In a named  range, is it the equivalent of doing a "for each"? Meaning, would
it check each of the cells in that range at once?

I've never really worked with named ranges before, but I have to say it
looks a lot cleaner, and quicker than what I've been doing, so I'm

Answer #3    Answered By: Murad Bashara     Answered On: Jan 31

I think we each answered quite different questions, actually. I answered
the literal question of how to copy  from a two-cell range  to another,
conditionally, but it seemed an add thing to want to do. The way you
interpreted the situation, though, makes more sense to me as something
worth coding!

You'd still need a 'for each' loop to evaluate each cell  in the range
individually if you wanted to do that.

I use range names all over the place, both in VBA and in formulae,
mostly because it makes a sheet easier to troubleshoot weeks after
you've moved away from it.

The next step is dynamic range names which are the best thing since

Answer #4    Answered By: Juan Reynolds     Answered On: Jan 31

Just to make sure I understand... If I had a range  (A1:A30 for example),
and I named  it data, to evaluate the values I'd have to say:

For each cell  in range("data")
If cell.value2 = whatever then do something
Next cell

Something like that? In your experience does the code seem to run faster
using range names, or is it just easier to troubleshoot later on?

Answer #5    Answered By: Rafael Thompson     Answered On: Jan 31

Yes, that's about the size of it. I haven't noticed speed implications,
I must say.

My number one speed tip is not to loop at all if you can help  it. If you
use Find and FindNext to get the values you want it's generally much,
much faster than looping through a large range.

Answer #6    Answered By: Helga Miller     Answered On: Jan 31

I'm assuming that you mean the data  you want copied  is actually in Column A
and not B as stated....(and if that's not the case you can easily adapt the
code). Hopefully this code will help  you out.

Public Sub newbie()
Dim year As Integer
Dim row As Integer

row = 2 'start at row 2 (that's assuming column titles are in row 1)

For Each cell  In Range("b2:b30") 'make range  larger if spreadsheet  is
If cell.Value2 = 2001 Then
Range("a" & row).Copy
Range("c" & row).PasteSpecial xlPasteValues
End If

If cell.Value2 = 2002 Then
Range("a" & row).Copy
Range("d" & row).PasteSpecial xlPasteValues
End If
row = row + 1 'move to the next row
Next cell 'check the next row's year

End Sub