Looping list of points through function to give answers

Hi All,

I have a list of numbers in Column E. In cell B2 I need to input the numbers from this list which will give me a corresponding answer in cell C12. I need to then paste this answer next to its original value, say in Column F. I need to then get the macro to loop this function through the entire length of the list in Column E. This list may change in length as well so I would need to have the macro determine the end for itself.

I am very much an amateur so any help would be greatly appreciated.

Thankyou in advance,

Spicey

Ok davesexcel, I have attached a very simple sample of what needs to happen. I thought it was simple enough not to have to attach.

In this instance, column E will have my raw data. I need to put each number into the input cell (B2) , highlighted in yellow. I then need to copy the answer this gives from the output cell (C12) into column F alongside the original input value. Obviously i have just made up some small function in this instance for illustration purposes but the real thing is much more detailed and no I cannot use functions in my macro to do it. I just need the macro to go through my raw data one at a time and copy and paste the values and then move down to the next row. I have done the first few as an example.

In my real spreadsheet I will have a list anywhere from 200 odd points to possibly thousands and thus I also need the macro to determine when the raw data finishes and not continue on. Hope this makes sense. I have got as far as recording the cut and paste parts for my macro but I dont know how to make this work in a loop like I have explained.

VBA_ListCalculatorFunction.xlsx

Any help is appreciated!

Cheers,

Spicey

This is what I have come up with so far but obviously doesnt work. I get an overflow error when trying to determine the Answer.

Sub Macro1()
'
' Macro1 Macro
'
Dim x As Integer
NumRows = Range("E2", Range("E2").End(xlDown)).Rows.Count
Range("E2").Select

For x = 1 To NumRows

    Dim ActiveCellSelect As Integer

    ActiveCellSelect = ActiveCell.Value

    Range("B2").FormulaR1C1 = ActiveCellSelect
    
    Dim Answer As Integer
    
    Answer = Range("C12").Value
    
    Range("F2").FormulaR1C2 = Answer
    
    ActiveCell.Offset(1, 0).Select
    
Next

        
End Sub

Shg, It appears you have not read my description properly. I have de-sensitised data so yes for this example I could do this without using macro... HOWEVER, for the actual spreadsheet I need to apply this to, I CANNOT! I must use a VBA. The spreadsheet contains multiple rows and collumns of calculations and adjustments to the Input value before giving the final output value. All this must stay as is with the formatting. THUS I require a simple macro to do the task that I have explained!

Regards,

Spicey

Does this not suggest using only the formula listed in C12?

In my real sheet there are many formulas applied through previoius cells in the working section before the output is given in C12

Ok managed to solve this myself, Took far longer than i would have preferred and a lot of trial and error, but this is the code that I ended up developing for my final sheet. I had two collumns of data i had to copy into two inputs before copying out the two outputs. Obviously cell references are different from the example i used as this is from my actual workbook but the jist is the same. My two data columns where Q and R.

Sub EastingsNorthings()

Dim x As Integer
NumRows = Range("Q3", Range("Q3").End(xlDown)).Rows.Count
Range("Q3").Select

For x = 1 To NumRows

    ActiveCell.Name = "LatStart"
    ActiveCell.Offset(0, 1).Name = "LongStart"
    ActiveCell.Offset(0, 2).Name = "LatFinish"
    ActiveCell.Offset(0, 3).Name = "LongFinish"

    Range("LatStart").Select
    
    Selection.Copy
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("LongStart").Select
    
    Selection.Copy
    Range("L4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("E40").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("LatFinish").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("K40").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("LongFinish").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Range("LatStart").Select
    
    ActiveCell.Offset(1, 0).Select
    
Next

        
End Sub

shg, If I was a bit more savvy I probably could simplify what i have greatly using your syntax.

No because I am not sure how to make this work as I actually have two columns of input data for my actual spread sheet and both numbers from these columns must be inputed into their respective cell prior to extracting both outputs. They cannot be done individually or one at a time as both inputs affect both outputs. Both outputs are then put into the 2 columns to the right of the original data.

How would I make it complete the first part of the With command for both cells before extracting both outputs? In my actual sheet I have raw data (lat and long) in columns Q and R. The input cells for these are F4 and L4 respectively and the output cells E40 and K40 respectively. These results are then placed in columns S and T.