Posts by tommeke

    Hello


    I created an Excel file where I calculate the reinforcing steel in a concrete structure.
    This structure always has the same dimensions except the height. The Excel file is automated this way that I only have to change the height and I automatically get the results (6 numbers, let's say u, v, w, x, y and z)


    Now is there a way (with an Excel formula or VBA) that I can make a second Excel sheet where I give a list of different heights with intervals of 0,25 and I automatically get the results (the 6 numbers) from the autmated sheet behind them?
    2,50 u v w x y z
    2,75 u v w x y z
    3,00 u v w x y z
    3,25 u v w x y z
    ...
    6,00 u v w x y z


    Thanks for helping

    Hello


    I created an Excel file where I calculate the reinforcing steel in a concrete structure.
    This structure always has the same dimensions except the height. The Excel file is automated this way that I only have to change the height and I automatically get the results (6 numbers, let's say u, v, w, x, y and z)


    Now is there a way (with an Excel formula or VBA) that I can make a second Excel sheet where I give a list of different heights with intervals of 0,25 and I automatically get the results (the 6 numbers) from the autmated sheet behind them?
    2,50 u v w x y z
    2,75 u v w x y z
    3,00 u v w x y z
    3,25 u v w x y z
    ...
    6,00 u v w x y z


    Thanks for helping

    Sub Macro3()



    Dim lngMyRow As Integer



    lngMyRow = Cells(Rows.Count, "E").End(xlUp).Row

    Range("E" & lngMyRow + 1).Font.Bold = True
    Range("E" & lngMyRow + 1).Formula = "=SUM(E17:E" & lngMyRow & ")"
    ActiveSheet.Rows("17:33").RowHeight = 13

    Dim iLoop As Integer

    With Range("E" & lngMyRow + 1)
    .Borders(xlDiagonalDown).LineStyle = x1None
    .Borders(xlDiagonalUp).LineStyle = x1None
    For iLoop = 7 To 12
    With .Borders(iLoop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Next
    End With

    End Sub


    This is what I have, basically I want to change the number "33" in the macro to the number ImgMyRow but I don't know how to do it.

    Re: Change number format for zero's


    Quote from Berdenis;759107

    Hi tommeke,


    Try changing the number formatting to:

    Code
    Sub test()
    
    
        Range("b17", Range("d" & Rows.Count).End(xlUp)).Offset(, 1).NumberFormat = "€ #,##0.00;€ -#,##0.00;""–"";@"
    
    
    End Sub


    Thank you, but if I use this each time a cell has the value "0" I get "-" as a result.

    Hi,


    I used this macro to change the number format in a certain range of cells.


    Sub GetalOpmaak()



    Range("b17", Range("d" & Rows.Count).End(xlUp)).Offset(, 1).NumberFormat = "€ #.##"

    End Sub


    But when a cell has a "0" in it it changes to: "€ , " instead of "€ 0,00".


    What did I do wrong?

    Re: trouble inserting formula via VBA


    Basically what I have now is a list of all my sheets from B17 to B (17 + i) depending on howmany sheets I have.
    What I'm trying to do now is get numbers from these sheets in this first sheet. In C17 D17 and E17 from the first sheet which is a resume of all other sheets. I want numbers from sheet 2 (F67 F75 & F77). Next to B18 (in C18 D18 & E18) from sheet 1 I want numbers from sheet 3 (F67 F75 & F77).

    Re: trouble inserting formula via VBA


    Thank you


    Currently I have this now:


    Code
    xWs.Range("C" & i + 15).Formula = "=INDIRECT(""""&B17&""!F67"")"


    It puts the same formule in every cell now, how can I make it so that the B17 changes to B & i +15 ?