Trying to write a macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to write a macro that begins a search about 45 rows from the top
of a spreadsheet. It will search column D for cells that not empty. When it
finds one, the macro should copy a coresponding value in column F and place
that value on another page in my workbook. This process needs to loop until
all non empty cells in column D have their corresponding column F values
copied and pasted. The problem is that I'm not very good with VBA or macros?
Can you help with this?

Thanks in advance and have a great day.
 
Hi John

let's give it a try
first of all, we need a loop, which loops 45 times, corresponding to the
rows you want to search through:

for i = 1 to 45

next i

after that we need to check if in this row the cell in column D is empty so
inside of the loop:

if cells(i, 4).formular1c1 = "" then

end if

now if the code finds a empty cell we have to enter data to the column f so
inside of the if:

cells(i,6).formular1c1 = "Whatever you like"

and now you want to put a value on another sheet:

sheets("yourotherpage").cells(x,y).formular1c1 = "Something else or whatever"

so the final code will look something like this

for i = 1 to 45
if cells(i, 4).formular1c1 = "" then
cells(i,6).formular1c1 = "Whatever you like"
sheets("yourotherpage").cells(x,y).formular1c1 = "Something else or
whatever"
end if
next i

hth, otherwise, just ask

Carlo
 
Carlo,

This is GREAT!! and it will definately help my overall VBA base of
info...but I can use a bit more if you can spare the time. In this particular
search, I'm not looking for empty cells in column D but cells that have
values already in place ( any value, not something specific ) and then I need
to copy a second number in the same row that is 2 columns to the right to
place on the other sheet. It is almost like a VLOOKUP but w/o specifying
what values to find. Thanks again. Your help very much appreciated.
 
Ah ok, so the value you want to copy is the value in the column F.

Ok, then it will look like this:

for i = 1 to 45
if cells(i, 4).formular1c1 <> "" then
'<> means not equal to, so it takes every cell which has something in it.
sheets("yourotherpage").cells(x,y).formular1c1 = cells(i,6).formular1c1
'x and y are the coordinates for your destination on the other sheet
end if
next i

hth......otherwise just call

Cheers Carlo
 
Hey again Carlo,

I have typed the macro code :) and checked and double checked it. I get a
run time error 13 whenever I attempt to run it. :( Since I'm bothering you
again. Does this code ( sheets("yourotherpage").cells(x,y).formular1c1 =
cells(i,6).formular1c1 ) specify that the data will be copied into cell (x,y
) as in beggining with cell A1 where x=A and y=1 and then each additional
piece of data will end up below as in B1, C1, etc? Thanks very much, I'm
trying to make sure I understand.
 
Back
Top