Posts by acat

    Two things:
    1. I need it to just scroll to last row in existing selection, not to make a different selection by selecting last cell, i.e. to keep existing selection which was also copied to clipboard before your proposed code
    E.g.: if range C6:F300 is the current selection I only need the code to scroll down to make that last row 300 visible, not to make any new selections, but do keep original selection copied
    2. Don't need it to scroll columns, only rows. Removing Selection.Columns.Count did not prevent scrolling of columns but prevented selecting cell in last row, instead selected cells seemingly at random mid current selection
    Hope you can see what I mean :)

    this selects a cell after last that has formula in column I select
    whereas
    code should select the last cell with data in that column, while ignoring cells that contain formulas but return blanks

    Maybe this will clarify: I am looking to improve code in my original post above with an InputBox which would allow to just click on a column header letter and then the code should select the last cell with data in that column automatically, while ignoring cells that contain formulas but return blanks
    Alternatively, without the use of InputBox, for code to just use active cell's column to accomplish the same

    Want to improve below code* with an InputBox which would allow to click on column header letter to pick the column, rather than having column hard coded (F in this case). As long as it will be locating last cell with data then no need to hard code the range either, but user input needed only for column
    *it locates last cell with data ignoring formulas which return blanks



    Code
    1. Sub LastRowIgnoreFormula()
    2. Cells(Application.Evaluate("MAX(IF(F6:F1005<>"""",ROW(F6:F1005)),0,1)"), "F").Select
    3. End Sub

    AlanSidman - (sorry, I was out for a week) latest code above does show correct cell address where value is < 0, however with below line un-commented, while it turns the cell address into a hyperlink, upon clicking the hyperlink dialog box pops up "Cannot open specified file", instead of selecting the target cell. With target cell being i22, when I right click into i3 containing the hyperlink and select "Edit Hyperlink" Address field shows "-338076"


    Code
    1. ActiveSheet.Hyperlinks.Add anchor:=Range("I3"), Address:=Range("I" & i)

    Somewhere in Range i5:i1000 there might be a cell with value under zero
    In cell i3 I'd like a formula showing address of such cell, but if none then word NONE
    Better yet, In cell i3 I'd like a formula returning a hyperlink to such cell showing it's address, but if none then word NONE


    Struggling with getting address of such cell, with following formulas:


    =IF(COUNTIF(I5:I1000,"<0"),CELL("address"),"no deductions")
    =CELL("address",INDEX(I5:I1000,MATCH("-",I5:I1000,0),1))
    =CELL("address",INDEX(I5:I1000,MATCH(COUNTIF(I5:I1000,"<0"),I5:I1000,0),1))

    Solved myself by recording placing that formula in, then editing my original code above:


    How do I solve this?


    Getting “run-time error 1004: Application-defined or object-defined error” on below line:

    Code
    1. Range("H10:H" & Lastrow).Formula = "=IF(D10="","",IFERROR(D10*1,""))"



    In this macro:

    Code
    1. Sub invFormulaInH()
    2. Dim Lastrow As Long
    3. Application.ScreenUpdating = False
    4. Lastrow = Range("G" & Rows.Count).End(xlUp).Row
    5. Range("H10:H" & Lastrow).Formula = "=IF(D10="","",IFERROR(D10*1,""))"
    6. Application.ScreenUpdating = True
    7. End Sub



    (probably don’t need to wrap it into ScreenUpdating lines either)