# Cell Value after Macro Run?

Page 1 of 1

## 5 Replies - 1023 Views - Last Post: 21 December 2010 - 02:48 AMRate Topic: //<![CDATA[ rating = new ipb.rating( 'topic_rate_', { url: 'http://www.dreamincode.net/forums/index.php?app=forums&module=ajax&section=topics&do=rateTopic&t=206283&amp;s=3f1acb057c5dff2241ab0c5b4f505e3b&md5check=' + ipb.vars['secure_hash'], cur_rating: 0, rated: 0, allow_rate: 0, multi_rate: 1, show_rate_text: true } ); //]]>

### #1 meatloaf

Reputation: 3
• Posts: 65
• Joined: 05-September 07

# Cell Value after Macro Run?

Posted 20 December 2010 - 12:35 PM

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

Is This A Good Question/Topic? 0

## Replies To: Cell Value after Macro Run?

### #2 raziel_

• Like a lollipop

Reputation: 466
• Posts: 4,262
• Joined: 25-March 09

## Re: Cell Value after Macro Run?

Posted 20 December 2010 - 01:54 PM

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

### #3 meatloaf

Reputation: 3
• Posts: 65
• Joined: 05-September 07

## Re: Cell Value after Macro Run?

Posted 20 December 2010 - 02:25 PM

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?

### #4 raziel_

• Like a lollipop

Reputation: 466
• Posts: 4,262
• Joined: 25-March 09

## Re: Cell Value after Macro Run?

Posted 20 December 2010 - 02:40 PM

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

### #5 thava

• D.I.C Lover

Reputation: 180
• Posts: 1,606
• Joined: 17-April 07

## Re: Cell Value after Macro Run?

Posted 21 December 2010 - 02:45 AM

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

use the worksheet change event to handle this

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

### #6 raziel_

• Like a lollipop

Reputation: 466
• Posts: 4,262
• Joined: 25-March 09

## Re: Cell Value after Macro Run?

Posted 21 December 2010 - 02:48 AM

and of course your totally correct