Posts by b0t

It works!!!

:kisshear:

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!

Thanks for the responses...there are only 4 lines of data. on the DT35 worksheet, so I don't think its a scale issue.

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!

:eureka::eureka::eureka: Thanks man! Great info!

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.

Dim FolderPath As String
Dim FileName As String


FolderPath = "C:\Users\jjones\Desktop\Macros\Testing\Division Files"
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""


Workbooks.Open (FolderPath & FileName), UpdateLinks:=0
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
 With Workbook.Worksheets(2)
 Range("E19").Select
    ActiveWindow.FreezePanes = True
    Rows("18:18").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$18:$18"
        .PrintTitleColumns = ""
    End With


Workbooks(FileName).Close SaveChanges:=True
FileName = Dir()
Loop

Thanks for your help!!!

Re: Computing Index/Match using data from another worksheet

Hi Everyone, just wanted to inform that I got around the problem by using a VLOOKUP instead. 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.

Sub test()




Sheets("query").Select
Sheets("query").Copy before:=Worksheets(2)




With Sheet2




'add columns
Cells(24, "an") = "In top conso prior month"
Cells(24, "ao") = "CTD Delta Margin in Top Conso Prior Month"
Cells(24, "ap") = "DT 35 in previous period"
Cells(24, "aq") = "CTD + DT35 previous period"
Cells(24, "ar") = "Delta"
Cells(24, "as") = "Adj Needed"












'add filters




Worksheets(2).AutoFilterMode = False
Rows("24:24").Select
Selection.AutoFilter
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=7, Criteria1:="="
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=3, Criteria1:= _
"Result"
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=39, Criteria1:="<-0.01", Operator:=xlOr, Criteria2:=">0.01"




'calculate columns




Dim LastRow As Integer
Dim RR As Variant
Dim VR As Variant




RR = Worksheets(3).Range("a:a")
VR = Worksheets(3).Range("am:am")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row








For x = 25 To LastRow




cells (x,"ao") = "=INDEX(VR,MATCH(cells(x,"a"),RR,0))"




Next x








End With








End Sub

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:

Dim LastRow As Integer
Dim RR As Variant
Dim VR As Variant




RR = Worksheets(3).Range("a:a")
VR = Worksheets(3).Range("am:am")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row








For x = 25 To LastRow




cells (x,"ao") = "=INDEX(VR,MATCH(cells(x,"a"),RR,0))"




Next x

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

Thanks Carim...I'll do that...

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.

With Sheet2


Dim LastRow As Integer
Dim RR As Variant
Dim VR As Variant




RR = Sheets(3).Range("a:a")
VR = Sheets(3).Range("am:am")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row








For x = 25 To LastRow




cells (x,"ao") = "=INDEX(VR,MATCH(cells(x,"a"),RR,0))"




Next x


End With

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
Rows("24:24").Select
Selection.AutoFilter
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=7, Criteria1:="="
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=3, Criteria1:= _
"Result"
ActiveSheet.Range("$A$24:$AS$16027").AutoFilter Field:=39, Criteria1:="<>", Operator:=xlFilterValues, Criteria2:="<>0"

Re: Lines of code are being skipped

Damn! I would have never figured that out. Much obliged Sir!

Re: Lines of code are being skipped

Wow dangelor, it works! Still trying to figure out what subtle changes you made...awesome stuff man, thanks!

Re: Lines of code are being skipped

I'd love to...please see attached...

thanks a million

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?