Posts by StefanG

  • Offset error in one, but not another workbook

    Re: Offset error in one, but not another workbook


    Hi Roy,
    i fear you're gonne hate me, but

    Quote

    You need a bracket ) at the end

    when i add the bracket, and work with this,

    Code
    Set firstblank = Cells(Rows.Count.End(xlUp).Offset(1, 0))

    it errors out on ".Count" with "compile error" - "Incorrect qualifier". I think i give up, and then again, now i want to know it.
    Stefan

  • Offset error in one, but not another workbook

    Re: Offset error in one, but not another workbook


    Hi Roy,

    Quote

    There's a typo, you have a comma instead of a full stop.

    Indeed, you are right, however, the code you gave is still erroring out. The comma came in when i fiddled with it. There is still something missing/wrong with it. Either a "(" missing or a ")" to much or else! A total of three "(" open and only two ")" close.

    Code
    Set FirstBlank = Cells(Rows.Count.End(xlUp).Offset(1,0)


    Stefan

  • Offset error in one, but not another workbook

    Re: Offset error in one, but not another workbook


    Hi,
    Thank you for your continued work with me on this.
    I am uncertain of the differences between xlDown and xlUp and could not find much that makes me any brighter on this either. Anything that makes me a grain smarter is appreciated.


    I suppose it would help to clarify what i am now looking to achive in order to not waste your time and to allow you to help me further.


    The spreadsheet i am working with (there are actually two, which have the same general set-up, for different data though) has data in columns A:P and the other A:M. Although most of the rows will have data in all columns, there are rows where any given number of cells, in any combination, may be blank.


    The first request was to force the user to use that last empty row. Now the request changed to remind the user to use the last row and not just any empty row.


    I would like to find the next available empty row and if the user is attempting to enter data in any row below that, have a msgbox telling the user that s/he should enter date in the last empty row, not the current selected one.


    Something like
    if current active row <> last empty row then msgbox


    Stefan

  • find next empty row and select cell in column A

    Hi,
    Any idea on how i can go about finding the next empty row (and select the first cell of that row (column A))?
    Thank you,
    Stefan


    edit:
    It probably should be noted that there are cells in Columns A through P. There are rows where all and/or just one cell contains data per row. So i cannot use a "lookup" based on a single column.

  • Offset error in one, but not another workbook

    Re: Offset error in one, but not another workbook


    Hi Batman,
    I will give that one a try when i get back - in about an hour.
    One thing i just noticed is, that i get an error, even if i select a cell not in column A. Something's funky.
    I appreciate the time you took to give me the other hint, i'll check on that and post back.
    Stefan

  • Offset error in one, but not another workbook

    Hi,
    Any idea why the following code would error out in line 2 on one workbook but not another? I copied the one line, then the two and then the entire code from one workbook to another. It works in one, but not in the other. (Error 1004)

    Code
    Dim FirstBlank As Range
Set FirstBlank = Range("A5").End(xlDown).Offset(1, 0)

    Thank you,
    Stefan

  • formulas

    Re: formulas


    Hi,

    Quote

    may be it cant be posible..

    Did you see and/or try the formulas i provided¿

    Quote

    =if(sheet1!A1="positive amount",sheet1!A1,"")
    =if(sheet1!A1="negative amount",sheet1!A1,"")


    Positive is everything greater then 0 and i considered 0 as positive (although not entirely correct - mathematically - i guess) hence ">=0", whereas Negative would be everything less then 0 and hence "<0". It would be up to you if you consider "0" values as Positive or Negative and therefore move the "=" from one formula to the other - from ">=0", which will turn it into ">0", to "<0" and get "<=0" instead.
    Worked for me.
    Stefan

  • formulas

    Re: formulas


    Hi,
    This may be a bit corky ( and i wouldnt be surpised if there is a different, better way), but works.
    On sheet (tab) 2,
    A1 =IF(Sheet1!A1>=0,Sheet1!A1,"")
    B1 =IF(Sheet1!A1<0,Sheet1!A1-Sheet1!A1*2,"")
    copy down the rows.
    Stefan

  • count dates in range

    Hello,
    Sheet1 (All)
    column F dates (m/d/yyyy)
    Sheet2 (Results)


    I want to count how many entries on "All" are in January, February etc.
    I got this (for April):
    =SUMPRODUCT((All!F1:F65000>=DATE(2006,4,1))*(All!F1:F65000<DATE(2006,5,1)))


    I'd like to change:
    The ranges F1:F65000 to column reference only as in F:F. What do i need to change to allow F:F?


    And (DATE(YYYY,M,D) to a cell reference (A10 on "Results") that currently holds a date (m/d/yyyy).


    I'm stuck and cant figure it out on how to get this working.


    Thank you,
    Stefan

  • Copy Cells

    Re: Copy Cells


    Hi fvcyo,
    I'm really not sure what you mean. However, I'm sure that, if you could provide an example of your work - what it is and what the result should look like - you'd get some good replies. Summing data from a range on sheet 1 on another sheet, and automatically updating the result, should one of the values change, could be done with =SUM(Sheet1!A1:A10). Is that what you are looking for?
    Stefan

  • Date/Time Formula

    Re: Date/Time Formula


    Hi,
    I believe it has to do with DateValue which is for dates, not time.


    So i suppose you have to use DateValue combined with TimeValue. I dont think that there is a one "code word" function that covers both.


    Use:

    Code
    Format(DateValue(TB2.Text), "mm/dd/yy") & Format(TimeValue(TB2.Text), " h:mm AM/PM")


    Stefan

  • If Vlookup Iserror = msgbox

    Re: If Vlookup Iserror = msgbox


    Hi Tony,

    Quote

    Evaluate

    I couldnt get that to work. I used instead (adjusted with file/references of course)

    Code
    ActiveCell.Formula = "=IF(ISERROR(VLOOKUP(RC[-2],'[LIST.xls]INFO'!C1:C3,3,FALSE)),""LL"",(VLOOKUP(RC[-2],'[LIST.xls]INFO'!C1:C3,3,FALSE)))"
        If ActiveCell.Value = "LL" Then

    and went from there to get my msgbox.etc.
    Still curious how i could get the eveluate part to work, just for the heck of it.
    Stefan

  • If Vlookup Iserror = msgbox

    Hi,
    I have

    Code
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(R[-1]C[-1],Sheet1!C[-1]:C[1],3,FALSE)),""Error"",(VLOOKUP(R[-1]C[-1],Sheet1!C[-1]:C[1],3,FALSE)))"

    which looks like this

    Code
    =IF(ISERROR(VLOOKUP(A1,Sheet1!A:C,3,FALSE)),"Error",(VLOOKUP(A1,Sheet1!A:C,3,FALSE)))

    in the spreadsheet.
    Can it be done to get a msgbox on error?


    And.
    Is there better way to get only the result of the vlookup above instead of using

    Code
    ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValues


    Stefan

  • Worksheet_SelectionChange - execute error

    Re: Worksheet_SelectionChange - execute error


    Nori,

    Quote

    Are you getting an error, or is the code not doing what you want?

    My bad. I do not get an error. It does not perform 100% the way i expect it to. It should not fire unless the user selects a cell in column A and the cell selected is the next empty one in column A.


    Say rows 1-10 in column A are used and contain data - Rows 1 - 3 column headers, row 4 conditional formatting. Panes frozen at B5.


    The user should select A11, if s/he does, add the formula in H11. Then if A12 or greater is selected, msgbox and exit sub. If the user selects B11:IV11, dont to a thing. - Yet, if the user enters a date in G11, the formula gets inserted, although A11:F11 - H11:IV11 is blank. This is where it performs incorrectly.


    Quote

    I can see one problem, you are trying to insert R1C1 formula using .Formula. Excel VBA isn't going to like that.

    The formula does get inserted and works. What would be the way you'd suggest though?


    Thank you,
    Stefan

  • Worksheet_SelectionChange - execute error

    Hi,
    I'd appreciate input to explain and workout the bug/error i am experiencing with the following.
    Goal:
    When the next empty cell in column A is selected, add a formula in the same row, column H.


    I'm not sure on how to hard code this and used the offset instead. So maybe instead of offsetting, i should/could change the references?


    However, as it is, it also adds the formla in column H when column G is selected and data is entered. I cant recreate this with any other column though.


    Thank you for your help.


    Stefan