Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

on If statement in a macro ?

  Asked By: Josefina    Date: Nov 27    Category: MS Office    Views: 769
  

I'm new to Ecel VBA(Fairly new to VB)
I have a macro that copies cells from sheet one to a sheet named DMH

Range("A2:A13").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A2:S13").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DMH").Select
Range("A2").Select
ActiveSheet.Paste
I Added this...
'Sheets("DMH").Select
'If ("d9") = "4" Then
'Sheets("DMH").Select
'Range("A63").Select
'ActiveSheet.Paste
End If

What I want is if in the cell range d2:D15 if one of the cells has a
value(month) let's say 4(will either be month(4) or null) then I want
to copy the same data to another part of the SS, (for 4 it would be
A63.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Edwin Chavez     Answered On: Nov 27

: Range("A2:A13").Select
: Range(Selection, Selection.End(xlToRight)).Select
: Range("A2:S13").Select
: Range(Selection, Selection.End(xlToRight)).Select
: Selection.Copy
: Sheets("DMH").Select
: Range("A2").Select
: ActiveSheet.Paste

This looks like a recorded macro. It is not necessary to do
all that selecting. This does the same thing without leaving a
copy of the range  in the clipboard.

ActiveSheet.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=Worksheets("DMH").Range("A2")


: I Added this...
: 'Sheets("DMH").Select
: 'If ("d9") = "4" Then
: 'Sheets("DMH").Select
: 'Range("A63").Select
: 'ActiveSheet.Paste
: End If
:
: What I want is if in the cell  range d2:D15 if one of the cells  has a
: value(month) let's say 4(will either be month(4) or null) then I want
: to copy  the same data  to another part  of the SS, (for 4 it would be
: A63.

Loop through each cell in the range and test its value separately.

Dim rngCell As Range

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If rngCell.Value = 4 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")

Next rngCell

End With

 
Answer #2    Answered By: Burk Martin     Answered On: Nov 27

This works great..
I don't know if my previous message posted, but..
I added:
Sheets("DMH").Activate
because I am running the macro  from the "ALLDATA" sheet
It's seemed to work??
Ok, now, I figured I'll just copy  past the above code
change a few things around for another worksheet and the same would
work for that?
Wrong...
I get "cannot change part  of a merged cell"
On the next part I added  for the "DMR" worksheet..

ActiveSheet.Range("A14:ad25", Range("A14:ad25").End(xlToRight)).Copy _
Destination:=Worksheets("DMR").Range("A2")

Am I supposed to reset something?

 
Answer #3    Answered By: Hubert Taylor     Answered On: Nov 27

But can I add the part  you wrote to the same macro?

I tried it like this:

ActiveSheet.Range("A2:ad13", Range("A2:ad13").End(xlToRight)).Copy _
Destination:=Worksheets("DMH").Range("A2")
Dim rngCell As Range

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")
'Copy to January
If rngCell.Value = 1 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A18")
'Copy to February
If rngCell.Value = 2 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A33")
'Copy to March
If rngCell.Value = 3 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A48")
'Copy to April
If rngCell.Value = 4 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")
'Copy to May
If rngCell.Value = 5 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")
'Copy to June
If rngCell.Value = 6 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A93")
'Copy to July
If rngCell.Value = 7 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A108")
'Copy to August
If rngCell.Value = 8 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")
'Copy to September
If rngCell.Value = 9 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A138")
'Copy to October
If rngCell.Value = 10 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A153")
'Copy to November
If rngCell.Value = 11 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A168")
'Copy to December
If rngCell.Value = 12 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A183")

Next rngCell

End With

But get an error:
On this..

.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")
I'm assuming it's because I'm on the ALLDATA sheet.
Becuase if I rum it from DMH sheet  it work fine.
I assume I have to name the sheet it's going to?

 
Answer #4    Answered By: Lurlene Fischer     Answered On: Nov 27

: But can I add the part  you wrote to the same macro?

You can do anything you wish. The question is: will it do what
you want it to do?

: I tried it like this:
:
: ActiveSheet.Range("A2:ad13", Range("A2:ad13").End(xlToRight)).Copy _
: Destination:=Worksheets("DMH").Range("A2")
: Dim rngCell As Range
:
: With Worksheets("DMH")
:
: For Each rngCell In .Range("d2:d15")
: 'Copy to January
: If rngCell.Value = 1 Then _
: .Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
: Destination:=.Range("A18")

[snip]
: 'Copy to December
: If rngCell.Value = 12 Then _
: .Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
: Destination:=.Range("A183")

You are thinking like a typist. Look for patterns. Think about
doing more with less. Avoid typing the same thing repeatedly. This
is untested, but ...

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then _
.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Next rngCell

End With

The first test (Int(rngCell.Value) = rngCell.Value) makes
certain we have an integer. The second test (rngCell.Value < 13)
makes certain the integer is not greater than 12. The third test
makes certain that the integer is not less than 1. If all tests
test true, the copy  happens.


: But get an error:
: On this..

<chastise>
You should have included the text of the error. Most languages
have hundreds of error messages. I do not know which one you
received and without your workbook I probably cannot reproduce the
error.
</chastise>


: .Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
: Destination:=.Range("A123")
: I'm assuming it's because I'm on the ALLDATA sheet.
: Becuase if I rum it from DMH sheet  it work fine.
: I assume I have to name the sheet it's going to?

It's that second range. I think it is missing the leading
period. Try this.

With Worksheets("DMH")

.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")


Which is short for this.

Worksheets("DMH").Range("A2:S13", _
Worksheets("DMH").Range("A2:S13").End(xlToRight)).Copy _
Destination:=Worksheets("DMH").Range("A123")

 
Answer #5    Answered By: Helene Stewart     Answered On: Nov 27

I'm almost there...
I think I can setup this for each worksheet.
I tried with the first, and came up witha problem,
I used what you wrote:
With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then _
.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Next rngCell

End With

This works except, if there is more than one value in d2:d15(ie, 4 on
two records) it copies it twice.
I tried putting "exit sub" after Destination... line
but then get nothing..

 
Answer #6    Answered By: Feodora Bonkob     Answered On: Nov 27

: I used what you wrote:

But, did you understand it.

: This works except, if there is more than one value in d2:d15
: (ie, 4 on two records) it copies it twice.

Well, that's what you told it to do. The loop checks each
value in the range  d2:d15. To stop on duplicate values, you'll
need to find a way to test for duplicates. The first step
would be to clearly define your criteria.

Are two copies really a problem? Each copy  on April should
produce the same result.


: I tried putting "exit sub" after Destination... line
: but then get nothing..

You mean like this?

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then _
.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Exit Sub

Next rngCell

End With

Now the algorithm only checks cell  d2, then exits the
sub. Cells d3:d15 are never checked. There is no logic in there
to check for duplicate values.

I think you were trying to do this, but it would stop after
the first valid value is reached and I am not clear whether that
is what you really want.

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then

.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Exit Sub

End If

Next rngCell


Your next step should not be programming anything. It should
be to articulate what you want to have happen when a duplicate
value is hit.

Also, I spotted an error in my code. To match your original
algorithm, Destination should be set to this. This still assumes
that originally the destination for May in your code was
mistakenly set to that of April (A63 instead of A78).

Destination:=.Range("A" & rngCell.Value * 15 + 3)

 
Didn't find what you were looking for? Find more on on If statement in a macro ? Or get search suggestion and latest updates.




Tagged: