IF or AND statements in macros

[scaryboy]

Everytime I have had a problem and turned to this newsgroup, my
problems have seemed monstrous. The solutions the wizards have come up
with, always seem so simple. Hopefully someone can help me again.


i need to write a macro that will search a range of cells, and copy
the values it finds to another cell. But, if the search cell is
empty, i do not want anything copied, i need the original values in
the target cells.

in the first range of cells, i have blanks that i will physically
input data to. i need the macro to find the values i have inputted,
and copy them to another different range, but only if there is data. i
do not want it to copy a blank space to the target cells because i
need the data in those cells until i determine a value.

i know i can write a formula that will place the original formula in a
cell if some other cell is blank, and place data from a non blank cell
there is that is the case. but, my problem is the target range is not
a single cell, it's a group of 3x3 cells. so, a number i write in the
orignal input cell, needs to be recognized as non blank, and then a
group of cells 3x3 must be selected and copied over with the single
piece of data essentially merging the 3x3 cells into one cell.

can i write a macro that will do something like, "If there is data
copy, If there is no data, skip to the next cell in the range to look
for data"?

i hope i explained that clearly.
if not i apologize and will try a different way to explain my problem.

thanks in advance.
fred

 

[Otto Moehrbach]

I understand everything you said except for the part where you define the
destination of the copied cells. That is not clear. This macro will do
what you want but, as you can see, the destination of the Copy is not there.
As written, this macro will do nothing. You need to add the destination
part. Please post back with more info on how to determine the destination
of each copied cell if you cannot write it into the macro yourself. Note
that you have to select all the cells that you want copied, blank cells and
all, before running this macro. This macro will skip over all the blank
cells in the selection. HTH Otto
Sub CopyCells()
Dim i As Range
For Each i In Selection
If Not IsEmpty(i.Value) Then
i.Copy 'Destination goes here
End If
Next i
End Sub

 

[scaryboy]

Thanks Otto for your help. I really appreciate it.

The destination cells will be exactly 28 columns to the left of the
orginal cells.

I fooled around with some formats for the destination, but once again
i'm lost. Could you let me know what the format of the destination
will look like? I hope it's clear. The data will copy and move along
the row to a different column.

Thanks again.
Fred R.

 

[Otto Moehrbach]

Fred
The macro below will copy all the cells in the selection, skipping the
blank cells, and paste each cell's contents into a cell that is offset 28
columns to the left. Don't forget to first select the entire range of cells
you want copied, blank cells and all. Note, in the macro, that the word
"Copy" must have a space after it. HTH Otto
Sub CopyCells()
Dim i As Range
For Each i In Selection
If Not IsEmpty(i.Value) Then
i.Copy i.Offset(, -28)
End If
Next i
End Sub

 

[scaryboy]

On Mon, 6 Mar 2006 06:55:00 -0500, "Otto Moehrbach"

Otto,
It's working, but it's not copying over the 3x3 cells. I can email you
a copy if you like. I don't think I am explaining this well. First of
all, what you've written for me is excellent.

The matrix on the left consists of a 27x27 grid, grouped in cells of
3x3. The matrix on the right is 9x9 but is the same physical size as
the matrix on the left. When I input data in the matrix on the right,
each number goes into a merged cell that used to be 3x3. I need that
data to be written over to the matrix on the left As it is, it writes
to the top left cell of the 3x3 box. I need to to paste over the
entire 3x3 area.

Also, if I manually cut and paste a few, then run the macro as I have
it, it gags because it can't copy over a merged cell. I think that
problem will fix itself when it can look to copy over the 3x3 boxes.

Thanks again for your time.
When do you sleep?
Fred R.

 

[Otto Moehrbach]

Fred
Send it to me but let me tell you up front that there will be a problem
if the destination cells are merged as you say. Merged cells is an Excel
feature from hell. It's a real nice feature until you want to copy to or
from merged cells.
My email address is (e-mail address removed). Remove the "nop" from this
address. HTH Otto