Microsoft Excel - Need Macro Help - Asked By Dan on 10-Mar-12 07:08 AM

The code in the second post above yours.

Dan
Donald Ross replied to Dan on 10-Mar-12 01:42 PM
Dan I am assuming that you are refering to your post need macro help under your eariler post vlookup. ______________________________________________________________________

Need Macro Help

Dan replied to Dan on 09-Mar-12 06:26 PM

Unfortunately I am having problems, but I think I am close. Any help would be appreciated.

So, I have tried to dreate a loop to go down column B and if a cell in Column "B" is anything other than a zero, copy the number from cell "I" and paste it in cell "N".

Sub Macro1()

Sheets("January").Select


Dim FirstCell As Range
Dim SecondCell As Range
Dim FirstTargetCell As Range

Dim i As Integer

Set FirstCell = Sheets("January").Range("B2")

Set SecondCell = Sheets("January").Range("I2")

Set FirstTargetCell = Sheets("January").Range("N2")

i = 0

Range("B2").Select

Do

If ActiveCell = 0 Then
ActiveCell.Offset(1, 0).Select

Else

''''''''''''''This next line is the issue. If cell B is a zero, do nothing and go to the next cell in B. Okay so far, now if cell B is anything other than a zero, take the number from cell I and put it in cell N. It is not doing it correctly and number are not in the right cell in cell N.

FirstTargetCell.Offset(i, 0).Value = SecondCell.Offset(i, 0).Value
i = i + 1


'''''''''''''The next lines are fine.

ActiveCell.Offset(1, 0).Select

End If

Loop Until FirstCell.Offset(i, 0).Value = ""

End Sub
_____________________________________________________________________________________

& I think you ment to add your file like Pichart suggested, your link did not make it.
Don


Dan replied to Donald Ross on 10-Mar-12 04:55 PM
The code in this post Macro1() is the code.  What is it I am not doing correctly?
Dan
Donald Ross replied to Dan on 10-Mar-12 05:55 PM
Dan,

I took your code and put it in a new book and it works fine for me
Here is your code in my book.

Dan New.zip

Dan since your code works fine for me the only thing I can think is maybe you have the code in the worksheet adn not in a module. I have done that, trying to make is trigger on open and placed it in the wrong place for what I was trying to do.
But anyway I hope this works on your system

Don

Donald Ross replied to Dan on 10-Mar-12 06:05 PM
Ok,

Dan sorry for the false hope there I just incountered an error or issue.  when your B range value is empty or zero the loop statement does not stop and will continue to run.  if all the values are 1 or better it works fine. 

So I am looking at it now. 

Maybe that will help you determine where your problem lies, and will help you fix it.  til then I will keep looking for you.

Don

Donald Ross replied to Donald Ross on 10-Mar-12 06:48 PM
Ok dan I found this for you.  

 ' Set Do loop to stop when two consecutive empty cells are reached.
    Do Until IsEmpty(ActiveCell) and IsEmpty(ActiveCell.Offset(1, 0))
     ' Insert your code here.
     '
     ' Step down 2 rows from present location.
     ActiveCell.Offset(2, 0).Select
    Loop

I am still learning but I this this will help you when the cell is =0 and not empty and your code wont keep running when it gets to teh end of the data in B

I am trying to get it working but again I am still learning.

Don

Donald Ross replied to Dan on 11-Mar-12 03:12 PM
FirstTargetCell.Offset(i, 0).Value = SecondCell.Offset(i, 0).Value
' only if the first cell is not empty... how do you make it skip to teh next active cell

Not matter if the firstcell is empty this line of code takes the value of the secondcell and puts it in hte firsttargetcell

I see where you are stepping through the active cells and if activecell=0 then you skip.  but as the it loops back to the above line it doesnot seem to skip a row it picks up where it let off and take the value from the secondcell adn puts it in the first targetcell.

I too am struggling with this perhaps wally or pichart would like to throw us a bone.. I will say it is fun learning.
Thanks again for your question.

Don
here is what I have so far for modification to your code.

Sub Macro1()
 
Sheets("January").Select
 

Dim FirstCell As Range
 Dim SecondCell As Range
 Dim FirstTargetCell As Range
 
Dim i As Integer
 
Set FirstCell = Sheets("January").Range("B2")
 
Set SecondCell = Sheets("January").Range("I2")
 
Set FirstTargetCell = Sheets("January").Range("N2")
 
i = 0
 
Range("B2").Select
 
' Set Do loop to stop when two consecutive empty cells are reached.
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
 

FirstTargetCell.Offset(i, 0).Value = SecondCell.Offset(i, 0).Value
' only if the first cell is not empty... how do you make it skip to teh next active cell
 
 i = i + 1
 
 
 ActiveCell.Offset(1, 0).Select
If FirstTargetCell = 0 Then
ActiveCell.Offset(1, 0).Select
 End If
Loop
 
End Sub


Dan replied to Donald Ross on 12-Mar-12 06:03 AM
Just want to say thank you for everyones input thus far.  Don I am looking at the code today and will let you know if it works.

Dan
Dan replied to Donald Ross on 12-Mar-12 08:26 AM
Okay, so i tried the code , and it still is not working right.  If column B has a zero or is blank then I want the matching cell in column N to be blank.  Right know if column B has a zero it is putting the number from colimn I into column N.
Stephen P replied to Dan on 14-Mar-12 05:35 PM
was trying to give a hand on this one, but can someone give me the abbreviated version of what is currently needed, it seems you just need a variable tied to the location, the variable could be either a for....next loop or a +1 counter
Stephen P replied to Stephen P on 14-Mar-12 05:39 PM
so if you are looking to have a blank in (X) column where there is a 0 in (W) column, something like this    

if(x1=0,Range("x" & 1).Value = " ", x1)

is that what you are looking for?
Donald Ross replied to Stephen P on 14-Mar-12 07:28 PM
Stephen,
Here is the workbook I was trying to help with.
The macro fills the targetcell with the data from the second cell based on a non-empty cell in first cell
The do until loop works but the active cell offset is not set right I know to help Dan it will need to skip the ROW with the empty first cell, and I am not sure how to make that happen.

Don
Dan V1.1.zip
Stephen P replied to Donald Ross on 15-Mar-12 10:58 AM
ok try adding the line where I inserted it below

FirstTargetCell.Offset(i, 0).Value = SecondCell.Offset(i, 0).Value
' only if the first cell is not empty... how do you make it skip to teh next active cell
If Range("b" & i + 2) = 0 Then Range("n" & i + 2).Value = ""

let me know how it works
Donald Ross replied to Stephen P on 15-Mar-12 11:02 AM
Stephen thats got it....
Donald Ross replied to Dan on 15-Mar-12 11:03 AM
Dan,

Stephen added this one line and vola! it works.
If Range("b" & i + 2) = 0 Then Range("n" & i + 2).Value = ""


Dan V1.2.zip

Don