Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to excute data
I try to modify the code below to copy data from column 1 cell value if next column 2 cell value is numeric and paste it to column 3. Also i try to assign "Activecell" to the assign column where data to be cop and paste. Hope someone in this forum may assist me to solve thi problem. 'From (before modify): Sub Loop() Do : If IsEmpty(ActiveCell) Then If IsEmpty(ActiveCell.Offset(0, -1)) Then ActiveCell.Value = "" Else ActiveCell.FormulaR1C1 = "=(RC[-2])" End If End If ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, -2)) End Sub 'to (after modify): Sub Loop2() Dim N As Integer Dim Celcal1 As Integer Dim cell1 As Object Celcal1 = Range("A1").CurrentRegion.Rows.Count For N = 1 To Celcal1 Set cell1 = Cells(N, 3) Do If IsEmpty(cell1) Then If IsEmpty(cell1.Offset(0, -1)) Then cell1.Value = "" Else cell1.FormulaR1C1 = "=(RC[-2])" End If End If cell1.Offset(1, 0).Select Loop Until IsEmpty(cell1.Offset(0, -2)) Next N End Sub :confused -- VBNewbi ----------------------------------------------------------------------- VBNewbie's Profile: http://www.excelforum.com/member.php...fo&userid=3652 View this thread: http://www.excelforum.com/showthread.php?threadid=56277 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to excute data
Not sure if this is you are after, as your description didn't match your
code, but... Sub Loop3() Dim N As Integer With Range("A1") For N = 0 To .CurrentRegion.Rows.Count - 1 If IsEmpty(.Offset(N, 2).Value) And IsNumeric(.Offset(N, 2).Value) Then .Offset(N, 2).FormulaR1C1 = "=(RC[-2])" End If Next N End With End Sub NickHK "VBNewbie" wrote in message ... I try to modify the code below to copy data from column 1 cell value if next column 2 cell value is numeric and paste it to column 3. Also, i try to assign "Activecell" to the assign column where data to be copy and paste. Hope someone in this forum may assist me to solve this problem. 'From (before modify): Sub Loop() Do : If IsEmpty(ActiveCell) Then If IsEmpty(ActiveCell.Offset(0, -1)) Then ActiveCell.Value = "" Else ActiveCell.FormulaR1C1 = "=(RC[-2])" End If End If ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, -2)) End Sub 'to (after modify): Sub Loop2() Dim N As Integer Dim Celcal1 As Integer Dim cell1 As Object Celcal1 = Range("A1").CurrentRegion.Rows.Count For N = 1 To Celcal1 Set cell1 = Cells(N, 3) Do If IsEmpty(cell1) Then If IsEmpty(cell1.Offset(0, -1)) Then cell1.Value = "" Else cell1.FormulaR1C1 = "=(RC[-2])" End If End If cell1.Offset(1, 0).Select Loop Until IsEmpty(cell1.Offset(0, -2)) Next N End Sub -- VBNewbie ------------------------------------------------------------------------ VBNewbie's Profile: http://www.excelforum.com/member.php...o&userid=36526 View this thread: http://www.excelforum.com/showthread...hreadid=562776 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to excute data
Many thanks NickHK, I tried to test your code line but i facing th compile error for the code line below: If IsEmpty(.Offset(N, 2).Value) And IsNumeric(.Offset(N, 2).Value) Then .Offset(N, 2).FormulaR1C1 = "=(RC[-2]) -- VBNewbi ----------------------------------------------------------------------- VBNewbie's Profile: http://www.excelforum.com/member.php...fo&userid=3652 View this thread: http://www.excelforum.com/showthread.php?threadid=56277 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to excute data
You're missing a "."
Then <fullstopOffset(N, 2).FormulaR1C1 = "=(RC[-2])" NickHK "VBNewbie" wrote in message ... Many thanks NickHK, I tried to test your code line but i facing the compile error for the code line below: If IsEmpty(.Offset(N, 2).Value) And IsNumeric(.Offset(N, 2).Value) Then Offset(N, 2).FormulaR1C1 = "=(RC[-2])" -- VBNewbie ------------------------------------------------------------------------ VBNewbie's Profile: http://www.excelforum.com/member.php...o&userid=36526 View this thread: http://www.excelforum.com/showthread...hreadid=562776 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to excute data
I replace it with fullstop but still compile error..???:eek -- VBNewbi ----------------------------------------------------------------------- VBNewbie's Profile: http://www.excelforum.com/member.php...fo&userid=3652 View this thread: http://www.excelforum.com/showthread.php?threadid=56277 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to excute data
That code works for me.
Just copy/paste. NickHK "VBNewbie" wrote in message ... I replace it with fullstop but still compile error..??? -- VBNewbie ------------------------------------------------------------------------ VBNewbie's Profile: http://www.excelforum.com/member.php...o&userid=36526 View this thread: http://www.excelforum.com/showthread...hreadid=562776 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
End of data loop | Excel Programming | |||
Need help with loop to import data | Excel Programming | |||
Need help with loop to import data | Excel Programming | |||
error to excute cut or copy in vba programming | Excel Programming | |||
Excute a Excel worksheet with Task Scheduler | Excel Programming |