[meatloaf]

The situation:
I have two objectives, reformat a spreadsheet and calculate the value of the '1's and '2's. But the user inserts the '1's and '2's after the reformatting of the spreadsheet, which is apart of the macro. So there are no numbers before I run the macro. If the numbers were already given before I ran the spreadsheet Id know how to do that:

Dim row As Integer
Dim oeValue As String
row = 5
While Not IsEmpty(Cells(row, 4))
    If Cells(row, 5) = "1" Then
        oeValue = oeValue + Cells(row, 4).value
    Else
        row = row + 1
    End If
Wend

But now should I set a cell where the total will be to sum up the '1' values once entered? I dont know how to do that.

Is there a way to "pause" the macro so the user can insert the numbers? Will I have to make two separate macros?


Columns:
D/4 = values
E/5 = numbers the user inserts, either a 1 or 2

[raziel_]

kinda you can loop in while cycle till there is a value in the cell.

[meatloaf]

NoBrain said:

you can loop in while cycle till there is a value in the cell.

i am unexperienced in loops. i tried this, with column B/2 now being value, and it did not work.

    row = 2
    Do
        If Cells(row, 5) = "1" Then
            oeValue = oeValue + Cells(row, 2).value
        End If
        row = row + 1
    Loop Until IsEmpty(Cells(row, 2))

I think I need something where it is the cell formula summing the values instead, so if the user edits the numbers after the macro, the total values will change.

Do you have any links on how to do this?

[raziel_]

what i was thinking is:

While Cells(row, 5) <> ""
DoEvents
Wend

so what this cycle do is loop till there is some value in the cell. now you can do a simple check if the value is correct using some variable like this:

bCheck=False
While bCheck=False
'Check if the cell have value and it is numeric and exit the cycle'
'by setting variable bCheck to true'
DoEvents
If Cell(row,5)<> "" And IsNumeric Cell(row,5) Then
 bCheck=True
End If
Wend

EDIT: code edit. i put do events in the second code because otherwise your application will hang out

This post has been edited by NoBrain: 20 December 2010 - 02:44 PM

[thava]

no i don't think that's the best way to do it

use the worksheet change event to handle this

view this link

http://www.ozgrid.co...cros-change.htm

[raziel_]

and of course your totally correct