Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro help
I would greatly appreciate if somebody could help me out creating a macro.
I need to create a lastRow variable from column B. Im not sure if this will work. Dim lastRow As String lastRow = Range(B2).End(xlDown).Row Then I need to loop from A2 to A & lastRow. During the loop: If it finds a number 0 in column A Then store the corresponding number two cells to the right (column C) as a variable. Then go over to column D and place number 1s downward (as many times as the stored variable number). Any help would be appreciated -- Regards, timmulla |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro help
I think this is the loop you asked for. but the rest is a little fuzzy. could
you clairify? Sub DoStuff() Dim YourRange As Range Dim ALastrow As Long Dim i As Variant Dim ws As Worksheet Dim x As Integer ALastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set YourRange = Range("A1:A" & ALastrow) Set ws = ActiveWorkbook.Worksheets(1) x = 1 For Each i In YourRange ws.Cells(x, 4).Value = "Not Sure What your trying to do" x = x + 1 Next End Sub "timmulla" wrote: I would greatly appreciate if somebody could help me out creating a macro. I need to create a lastRow variable from column B. Im not sure if this will work. Dim lastRow As String lastRow = Range(B2).End(xlDown).Row Then I need to loop from A2 to A & lastRow. During the loop: If it finds a number 0 in column A Then store the corresponding number two cells to the right (column C) as a variable. Then go over to column D and place number 1s downward (as many times as the stored variable number). Any help would be appreciated -- Regards, timmulla |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro help
Im trying to loop through column A starting from row 2 until the last row.
Column A includes blanks so I need to set the LastRow based on column B. The following code sets up the range that I want to loop through. ie. YourRange Lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set YourRange = Range("A2:A" & Lastrow) Set ws = ActiveWorkbook.Worksheets("Macro Development") Example of what I'm trying to accomplish: Lets say during the loop it finds a 0 in cell A10. I need it to store the number two columns to the right (ie. C10) as a variable. For this example lets say that cell C10 is 5. Then I need to go over one column and place 1's downward as many times as the stored variable. So I would need 1's placed from D10 to D15. Hope this makes sense. I appreciate your help. -- Regards, timmulla "Office_Novice" wrote: I think this is the loop you asked for. but the rest is a little fuzzy. could you clairify? Sub DoStuff() Dim YourRange As Range Dim ALastrow As Long Dim i As Variant Dim ws As Worksheet Dim x As Integer ALastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set YourRange = Range("A1:A" & ALastrow) Set ws = ActiveWorkbook.Worksheets(1) x = 1 For Each i In YourRange ws.Cells(x, 4).Value = "Not Sure What your trying to do" x = x + 1 Next End Sub "timmulla" wrote: I would greatly appreciate if somebody could help me out creating a macro. I need to create a lastRow variable from column B. Im not sure if this will work. Dim lastRow As String lastRow = Range(B2).End(xlDown).Row Then I need to loop from A2 to A & lastRow. During the loop: If it finds a number 0 in column A Then store the corresponding number two cells to the right (column C) as a variable. Then go over to column D and place number 1s downward (as many times as the stored variable number). Any help would be appreciated -- Regards, timmulla |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro help
Try this.
Sub DoStuff() Dim YourRange As Range Dim ALastrow As Long Dim i As Variant Dim ws As Worksheet Dim x, j As Integer Dim yourVar As Integer ALastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set YourRange = Range("B1:B" & ALastrow) Set ws = ActiveWorkbook.Worksheets(1) For Each i In YourRange yourVar = i.Offset(0, -1).Value If i.Offset(0, -1).Value = "0" Then i.Offset(0, 1).Value = i.Offset(0, -1).Value For j = 1 To yourVar i.Offset(0, 2).Cells.Insert Shift:=xlDown i.Offset(0, 2).Value = "1" Next j End If Next i End Sub "timmulla" wrote: I would greatly appreciate if somebody could help me out creating a macro. I need to create a lastRow variable from column B. Im not sure if this will work. Dim lastRow As String lastRow = Range(B2).End(xlDown).Row Then I need to loop from A2 to A & lastRow. During the loop: If it finds a number 0 in column A Then store the corresponding number two cells to the right (column C) as a variable. Then go over to column D and place number 1s downward (as many times as the stored variable number). Any help would be appreciated -- Regards, timmulla |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro help
Thanks, I made a few tweaks and got it to work.
-- Regards, timmulla "Office_Novice" wrote: Try this. Sub DoStuff() Dim YourRange As Range Dim ALastrow As Long Dim i As Variant Dim ws As Worksheet Dim x, j As Integer Dim yourVar As Integer ALastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Set YourRange = Range("B1:B" & ALastrow) Set ws = ActiveWorkbook.Worksheets(1) For Each i In YourRange yourVar = i.Offset(0, -1).Value If i.Offset(0, -1).Value = "0" Then i.Offset(0, 1).Value = i.Offset(0, -1).Value For j = 1 To yourVar i.Offset(0, 2).Cells.Insert Shift:=xlDown i.Offset(0, 2).Value = "1" Next j End If Next i End Sub "timmulla" wrote: I would greatly appreciate if somebody could help me out creating a macro. I need to create a lastRow variable from column B. Im not sure if this will work. Dim lastRow As String lastRow = Range(B2).End(xlDown).Row Then I need to loop from A2 to A & lastRow. During the loop: If it finds a number 0 in column A Then store the corresponding number two cells to the right (column C) as a variable. Then go over to column D and place number 1s downward (as many times as the stored variable number). Any help would be appreciated -- Regards, timmulla |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |