Looping formula...

[stenlake1]

Hi all,

I have attached a spreadsheet. I need a script to go through column J, and whenever it finds a value, to do the following in the adjacent cell in column K (see formula in the spreadsheet). NOw when it finds a value in J, I need the user to be prompted for a value which will be used in the formula.

However, if the Line (column A) is the same, the formula will use the same user value and only when a new value in column A occurs, will the user be required to input a new value. Does this make sense? Hopefully the spreadsheet will make it clearer

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim ans As Variant

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1, TEST_COLUMN).Value Then
.Cells(i, "K").Formula = "=(RC2-R[-1]C2)*" & ans & "/1000"
ElseIf .Cells(i, TEST_COLUMN).Value = .Cells(i + 1, TEST_COLUMN).Value Then
Do
ans = InputBox("Supply value for row " & i)
Loop Until ans <> ""
End If
Next i

End With

End Sub

[/vba]

[mdmackillop]

Beaten to it, but here's my solution anyway.
[vba]Sub Macro1()
Dim uservalue As Single, cel As Range
For Each cel In Intersect(Range(Cells(3, "J"), Cells(Rows.Count, "J").End(xlUp)), ActiveSheet.UsedRange)
If cel <> "" Then
If cel.Offset(-1, 1) = "" Then
uservalue = InputBox("Enter User Value")
End If
cel.Offset(, 1).FormulaR1C1 = "=((RC[-9]-R[-1]C[-9])*" & uservalue & "/1000)"
End If
Next
End Sub
[/vba]

[Bob Phillips]

As I understand it, he wants a new input value per block.

[mdmackillop]

and that's what he gets!

[stenlake1]

Thank you both.

XLD: Rather than the row number in the prompt, I would like the Line Value (column A) instead. Can I do that?

Additonally if there is only one value for the line, I do not want the procedure to run and for it to goto the next one.

Finally, when it has the line it is asking for you to input a value, for it to be selected on the screen?

Are these things possible?

Many thanks.

[Bob Phillips]

NOt sure what you mean by ... if there is only one value for the line, I do not want the procedure to run ..., but for the rest

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim ans As Variant

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow 'iLastRow to 1 Step -1
If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1, TEST_COLUMN).Value Then
.Cells(i, "K").Formula = "=(RC2-R[-1]C2)*" & ans & "/1000"
ElseIf .Cells(i, TEST_COLUMN).Value = .Cells(i + 1, TEST_COLUMN).Value Then
Do
.Cells(i, TEST_COLUMN).Select
ans = InputBox("Supply value for the " & .Cells(i, TEST_COLUMN).Value & " block")
Loop Until ans <> ""
End If
Next i

End With

End Sub
[/vba]

[stenlake1]

Hi xld,

Thats brilliant, thank you. I want it so if they press cancel, it gives them an option - goto the next block or to stop the script completely - any ideas?

Many thanks

[unmarkedhelicopter]

This is known as "feature creep"

[stenlake1]

you are probably right, but I am an amateur developer so find things to make it better as I go along - I apologise my project management has not been great!

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim ans As Variant

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1, TEST_COLUMN).Value Then
.Cells(i, "K").Formula = "=(RC2-R[-1]C2)*" & ans & "/1000"
ElseIf .Cells(i, TEST_COLUMN).Value = .Cells(i + 1, TEST_COLUMN).Value Then
.Cells(i, TEST_COLUMN).Select
ans = InputBox("Supply value for the " & .Cells(i, TEST_COLUMN).Value & " block")
If ans = "" Then
If MsgBox("Continue and skip this block (OK), or quit the job (Cancel)?", vbOKCancel) = vbCancel Then
Exit For
Else
Do
i = i + 1
Loop Until .Cells(i, TEST_COLUMN).Value <> .Cells(i - 1, TEST_COLUMN).Value
End If
End If
End If

Next i

End With

End Sub
[/vba]

[stenlake1]

Thank you XLD, it works

I understand I am asking a lot so feel free not to answer my next question but I want to develop it a tiny bit further. In some examples the every block will have the same <user value>. So I would like the user to be first prompted with:

"Is the value the same for each line?" <Yes/No>

If yes, the user enters a value which is used for every block.

If no, the user is taken through every block individually as before.

Any thoughts?

Thank you for your help.

[Bob Phillips]

How about pre-loading the input box with the previously entered value?

[stenlake1]

Basically, if the user knows the value will be 100 for all blocks, I dont want them to have type in 100 a lot of times when they can just input 100 once to be applied for all blocks.