PDA

View Full Version : loop



booyw
01-04-2019, 02:56 AM
Good day
I m stugling with a lopp
I want is sheet"Formula"cell A1 t bring the cell A2 of "Fron Page
Do its calculation
then do everyhing agin ut with cell A3 of sheet "Front Page
"now "Front Page"column A can be 100 cell or eve 1000 cells
how do I do he loopuntil last cell with data has been done


example attached

booyw
01-04-2019, 08:10 AM
:dunno

Paul_Hossler
01-04-2019, 04:38 PM
1. Welcome to the Forums - I moved your post to the Excel forum from Access - take some time to read the FAQs in my signature

2. Since we're all volunteers, 5 hours is not much time to ping for a solution

3. Trying to read your post #1 was hard, but I think I sort of figured out what you were trying to do

I didn't understand why you used what seems to be a very round about way, but you can try this version which seems more straight forward






Sub test()

Dim FP As Worksheet, Fm As Worksheet
Dim rFP As Range, r As Range

Set FP = ThisWorkbook.Worksheets("Front Page")
Set Fm = ThisWorkbook.Worksheets("Formula")


Set rFP = Range(FP.Range("A2"), FP.Cells(FP.Rows.Count, 1).End(xlUp))


For Each r In rFP.Cells
Fm.Cells(Fm.Rows.Count, 4).End(xlUp).Offset(1, 0).Value = r.Value / 2
Next

End Sub

mana
01-04-2019, 05:55 PM
Sub test()
Dim FP As Worksheet, Fm As Worksheet
Dim r As Range

Set FP = ThisWorkbook.Worksheets("Front Page")
Set Fm = ThisWorkbook.Worksheets("Formula")
'
Set r = FP.Range("A2", FP.Range("A2").End(xlDown))
Fm.Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(r.Count).Value = Evaluate(r.Address(, , , True) & "/2")

End Sub

mana
01-04-2019, 06:17 PM
Sub test2()
Dim FP As Worksheet, Fm As Worksheet
Dim v

Set FP = ThisWorkbook.Worksheets("Front Page")
Set Fm = ThisWorkbook.Worksheets("Formula")
'
v = FP.Range("A2", FP.Range("A2").End(xlDown)).Value
v = WorksheetFunction.MMult(v, 0.5)
Fm.Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(UBound(v)).Value = v

End Sub