Posts by eduboys

Re: rounding to the nearest .25

Probably inefficient, but you could do it with a formula...

Lets say your number was in A1

=INT(M2)+IF((M2-INT(M2))<0.25,0,IF((M2-INT(M2))<0.5,0.25,IF((M2-INT(M2))<0.75,0.5,0.75)))

Re: Debugging setting ranges equal to each other.

I changed the Macro to use cut and paste, and everything works fine, just curious why the above code did not work.

I'm running into a problem with a particular row when copying this information from one sheet to another. This has worked for the last month without a problem, but one particular cell broke it today, and I cannot determine why. Below is my VBScript.

Dim wsOpen As Worksheet
Dim wsAll As Worksheet
Dim MyLastRow As Long
MyLastRow = Application.CountA(ActiveSheet.Range("A:A"))


'Copy and paste all defects into the Open and Pending Issues Sheet
    With wsOpen
        .Range(.Cells(1, 1), .Cells(MyLastRow, 19)) = _
        wsAll.Range(wsAll.Cells(1, 1), wsAll.Cells(MyLastRow, 19)).Value
    End With

[Code]
The cell that is breaking my macro is below (I removed actual text, but left all punctuation):

Yada yada -

1) Yada yada yada - yada yada 1

2) Yada yada, yada yada yada. Yada yada 8 yada

3) Yada yada - yada yada - Yada yada. yada yada 2

4) Yada yada. yada - yada 1 yada 23 yada = 1, yada = yada. Yada yada

Re: Setting ranges equal to each other between 2 sheets

Worked like a charm... I'm still getting used to coding in VBA. Thank you for the quick help!!

In an attempt to optimize my macro, I want to set ranges equal to each other instead of copying and pasting values. Is this possible across 2 different worksheets?

I cannot determine why this code segment is not currently working:

Sheets("Open and Pending Issues").Range(Cells(1, 1), Cells(MyLastRow, 18)) = Sheets("All Issues").Range(Cells(1, 1), Cells(MyLastRow, 18)).Value

If I substitute the following, the code works fine:

Sheets("Open and Pending Issues").Range("A1:S331") = Sheets("All Issues").Range(Cells(1, 1), Cells(MyLastRow, 18)).Value

Thanks in advance!

Re: PasteSpecial

Is this much faster than using the copy and paste functions?

Re: Variable Print Area

Not sure how to search for the row of the last subtotal, but I know you need to set a range. In the example below you go to the bottom of all rows and select that range.

[vba]
Range("C5:Q5").Select
Range(Selection, Selection.End(xlDown)).Name = "PrintRangeName"

ActiveSheet.PageSetup.PrintArea = "$C$5:" & "PrintRangeName"
[/vba]