Posts by b0t

    Hello Ozgrids!

    I've run into a problem with my range declaration that I cannot debug. The problem looks like this...

    Dim lasttab As String
    Dim Arg1 As Range
    lasttab = Sheets(Worksheets.Count).Name
    [B]Set Arg1 = Sheets(lasttab).Range("x: x")[/B]

    The bolded line is giving me an error and I can't figure out what is wrong. All the other variables are being read properly. Much obliged for any feedback. Thanks!

    Hello again beautiful Ozgrid people. I've run into a problem with the following script that gives me a run time error when reading the the 3rd LastRow declaration. I really don't know what the problem is. When I change the reference to sheet(6), it tells me that the last row is 1 even though there are four rows of data there. The first two LastRow declarations are being read with no problem. Any help would be, as always, greatly appreciated.

    Dim LastRow As Integer
    Dim LastRow2 As Integer
    Dim LastRow3 As Integer
    Dim arr As Range
    Dim arr2 As Range
    LastRow = Sheets("Analysis").Cells(Rows.Count, 1).End(xlUp).Row
    LastRow2 = Sheets("Previous Period DT37").Cells(Rows.Count, 1).End(xlUp).Row
    LastRow3 = Sheets("Previous Period DT35").Cells(Rows.Count, 1).End(xlUp).Row
    Set arr = Sheets("Previous Period DT37").Range(Sheets("Previous Period DT37").Cells(1, 2), Sheets("Previous Period DT37").Cells(LastRow2, 4))
    Set arr2 = Sheets("Previous Period DT35").Range(Sheets("Previous Period DT35").Cells(1, 2), Sheets("Previous Period DT35").Cells(LastRow3, 4))

    Thanks again!

    Yup...that fixed the jumping. Getting an error message with:

    With Workbook.Worksheets(2)

    whether i use the activesheet declaration or just .pagesetup. Once I remove it...the code runs alright. The files contain a hidden first sheet which is why I wanted to refer to worksheet (2) but i'm wondering if that is necessary.

    Thanks a million!

    Hi Chirayuw!

    Thanks for the quick feedback. I tried running the code both with the activesheet declaration and without. However I continue to get the same problem. Also, like you pointed out, no error message. A bit of a head scratcher for sure!

    Hi There,

    I'm having trouble with a piece of code that i've used in the past but for some reason is now skipping without performing the intended formatting. The idea is to open each file in a specified folder and perform a formatting function before saving it again. Right after the Do While command, it just skips to the bottom and I can't understand why.

    Thanks for your help!!!

    Re: Computing Index/Match using data from another worksheet

    Hi Glovner!

    Thanks for the fast reply. I think I may have not provided enough context. The loop doing the index/match is supposed to run on worksheet 2 (and thus also the lastrow calculation) but the values are being pulled from worksheet 3. I have embedded the full code so that you can see the whole scope of what the macro is doing (beyond this particular problem). You will notice that I have referenced a

    With Sheet2

    earlier in the sequence to deal with the formatting.

    Thanks again for your help! I'll try posing the workbook ASAP, for some reason i've been having difficulties....

    Hello again!

    I was recommended by Carim to start a new thread on this after I posted in an old thread. I am trying to write an an Index Match function that looks across worksheets. Essentially the index match is taking a list of values from sheet 2 and then cross-referencing and pulling values from sheet 3. I am getting a compile error because it expects and end of statement and I might be blind but I can't see what's wrong. This is the piece of code that i'm referring to:

    For some reason I can't attach the workbook. I'll keep trying...thanks again for everything.

    Re: Computing Index/Match using data from another worksheet

    Hi there,

    I thought I'd revive this old thread because I am trying to achieve the same thing with a much simpler code however I am getting a compile error because it expects and end of statement yet i'm not sure what's up. Essentially the index match is taking a list of values from sheet 2 and then cross-referencing and pulling values from sheet 3. Thanks again for everything. This site rocks.

    Re: Filter not working for second criteria

    Thanks guys...I realized that my operator was wrong and also that I can only filter for max two criteria per column without a helper column.

    Cheers for the quick responses...

    Hi Again!

    I wrote a filter in VBA for three columns in my spreadsheet but for some reason it refuses to ignore the second condition of the last filter to exclude the zero values. I'm kind of scratching my head as to why this is so.

    The first filter looks for blanks
    The second filter looks for "Result"
    The third filter gets rid of blanks and zeroes

    As always....thanks a million for the support...

    .AutoFilterMode = False
    ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=7, Criteria1:="="
    ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=3, Criteria1:= _
    ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=39, Criteria1:="<>", Operator:=xlFilterValues, Criteria2:="<>0"

    Re: Lines of code are being skipped

    Hi dangelor,

    I believe the statement has to be "and" rather than "or" because the "or" statement will always be true and I just want to copy the cells that have a value in them. However, once I put the "and" statement in, it doesn't seem to recognize the two cells that have values...confusing!

    Re: Lines of code are being skipped

    PS....One last thing...its seems that the condition:

    If .Range("L" & x) <> "" And .Range("L" & x) <> "0" Then

    Is being ignored. Its returning all values and not filtering out for blanks or zeroes...

    something to do with my range not being defined perhaps?