ROund Up

[jaangel]

I have a macro that pulls data and performs a few additions and divisions before putting the values into Cells B4:B35. I need these to round up to the next number with no decimals. I currently have this after the data pulling/manipulation. I found this code on ehow and I cannot figure out what to put for the a1 = CDbl(InputBox("enter number you want to round"))

here is the code that I would like to happen but i am not sure if this code will even work for what I am trying to do since there is a message box that pops up and wnats me to enter a number. any suggestions would be appreciated. thanks

Dim a1, a2, s

a1 = CDbl(InputBox("B7.Value"))

a2 = CInt(InputBox("0"))

s = "=Roundup(" & a1 & "," & a2 & ")"

Range("B7").Formula = s

Range("B7").Calculate

MsgBox (Range("B7").Value)

[jaangel]

I have also tried this code but it just puts all the values to 0

Dim cellvalue As Double

For Each cell In Range("B4:B35")

cell.Value = Application.WorksheetFunction.RoundUp(cellvalue, 0)

Next

[ImStevenB]

Hi jaangel,

Your last bit of code set everything to 0 because you never set the value of cellvalue.

Try this

Please Login or Register  to view this content.

Note the line that says cellvalue = cell.

Hope This Helps.

Steve

[jaangel]

When I try this it gives erro 13 type mismatch for the line cellvalue = cell

[ImStevenB]

What kind of values do you have in the Range B4:B35?

I can run this bit of code with positive and negative numbers. If you are using less than or greater than symbols (or any text identifier in the cell) it will give that error.

[jaangel]

The values within these cells are values that are given by another VBA code that pulls two numbers (two different cells) from a closed workbook adds them together then divides them by 4, then the resulting value is placed into B4,B5, B6,... so on through B4:B35.

[jaangel]

Previous post means the cells are blank but then have a value once the code is ran.

[ImStevenB]

Can you post the code that is placing the values? Might be something in there that is placing a character that is causing the mismatch.

I tried several ways of formatting the cell and placing the number in the cell. The only way I get the mismatch is if there is a text character in the cell.

[jaangel]

Dim wbSizing As Workbook
Dim wsStaging As Worksheet
Dim wbShipping As Workbook
Dim wsSchedule As Worksheet
Dim strName As String
Dim cellvalue As Double

Set wbSizing = ActiveWorkbook
Set wsStaging = wbSizing.Worksheets("Staging")
Set wbShipping = Workbooks.Open("F:\Prod\RELEASES\ship" & Format(wsStaging.Range("B39").Value, "mmddyy") & strName & ".xls")
Set wsSchedule = wbShipping.Worksheets("wksch1pp")


wsStaging.Range("B4").Value = (wsSchedule.Range("E4").Value + wsSchedule.Range("G4").Value) / 4


And yes i could not get a For Each cell in Range ("B4:B35") code to work so I have this above addition line repeating for diferent cells from B4:B35 which is a forearm burner to type.