• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Advanced Filter [VBA]

vijay.vizzu

vijay.vizzu

Member
Dear All,

I am facing one problem that, i have a named cell (Z3) as valCDC in my below code. If i go through the code one by one by pressing F8, then the valCDC was showing correct answers, but it can't put the value in valCDC (cell Z3). Due to that, advanced filter can't able to filter the required valued data. so please check and help

[pre]
Code: 
For Each cell In Range("valTotal")
If cell.Value = "0" Then
cell.Offset(1, 0).Select
Else
[valCDC] = cell.Offset(0, -8).Select
Range("fltrange").AdvancedFilter _
Action:=xlFilterCopy, _
criteriarange:=Range("fltcriteria"), _
copytorange:=Range("v5"), _
unique:=False
Range(Range("extract"), Range("extract").End(xlDown)).Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:=currpath & valCDC & Format(Now, "dmmmyyy-hhmmss") & ".xlsx",
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range(Range("extract"), Range("extract").End(xlDown)).Clear
Range("z3").ClearContents
End If
Next cell
[/pre]
 
Double-check this line

Code: 
[valCDC] = cell.Offset(0, -8).Select

I'm guessing it should be a [code]Value
or something, rather than Select[/code]?
 
Sorry Luke M, its .value. The problem was, it extracts data, but file name not started with cell value, it starts with date and if i will change [valCDC] as valCDC file name starts with cell value but data extraction can't done. Please suggest where i was wrong.

[pre]
Code: 
Sub extractData()
Dim cell As Range
Dim currpath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

currpath = ActiveWorkbook.Path & ""
Range("extract").Select
Range(Selection, Selection.End(xlDown)).Clear
Range("t4").Select

For Each cell In Range("valTotal")
If cell.Value = "0" Then
cell.Offset(1, 0).Select
Else
[valCDC] = cell.Offset(0, -8).Value
Range("fltrange").AdvancedFilter _
Action:=xlFilterCopy, _
criteriarange:=Range("fltcriteria"), _
copytorange:=Range("v5"), _
unique:=False
Range(Range("extract"), Range("extract").End(xlDown)).Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:=currpath & Range("z3").Value & Format(Now, "dmmmyyy-hhmmss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range(Range("extract"), Range("extract").End(xlDown)).Clear
Range("z3").ClearContents
End If
Next cell
Application.ScreenUpdating = True
Application.ScreenUpdating = True
MsgBox "Data splitting done successfully!", vbInformation + vbOKOnly
End Sub
[/pre]
 
Hi all,


Please help someone, the problem is while saving the file name with below code

ActiveWorkbook.SaveAs Filename:=currpath & valCDC.Value & Format(Now, "dmmmyyy-hhmmss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


files are saving with date not with cell value which is in valCDC (a ranged name Z3). Please help how to resolve it


Regards

vijay
 
Hi Vijay ,


valCDC.Value is wrong , since to refer to a range , you have to use either Range("valCDC") or [valCDC]


Can you explain this sentence of yours , with two examples , one where it does not extract data , and one where it does but without the cell value ?

[pre]
Code: 
The problem was, it extracts data, but file name not started with cell value, it starts with date
and if i will change [valCDC] as valCDC file name starts with cell value but data extraction can't done.
[/pre]
Narayan
 
Thank you so much Narayank991 for your comment


1. where it does not extract data , and one where it does but without the cell value ?

Ans. initially, i have put [valCDC] = cell.Offset(0, -8).Select, the value selected by offset, but it can't able to put the values in the required cell i.e. valCDC (cell Z3). Due to this, advanced filter can't able to extract the required data. As per luke, i have changed it as [valCDC] = cell.Offset(0, -8).Value. It works fine, it selects the value by offset and putting the value in the cell Z3 i.e. valCDC and advanced filer extracts the data from the range.


2.The problem was, it extracts data, but file name not started with cell value, it starts with date

and if i will change [valCDC] as valCDC file name starts with cell value but data extraction can't done.

Ans. After extraction and pasting the data in new workbook, i want to save it with the value of valCDC(Z3). To achieve that, i used ActiveWorkbook.SaveAs Filename:=currpath & valCDC.Value & Format(Now, "dmmmyyy-hhmmss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, but it doesn't took the value of valCDC.


I hope you got my point, extremely sorry for not giving any example.


So please help me, how to save the file with value of valCDC.


Regards

Vijay
 
Addition with previous comment


due to lack of information that valCDC & [valCDC] both are different, valCDC treat as variable whereas [valCDC] will treat as named range.


if i will change [valCDC] as valCDC, then file saving are done with value of valCDC, but data extraction is going wrong, it doesn't extract the data as per criteria, becoz it doesn't putting the value in the named range.
 
Hi Vijay ,


I have still not understood your problem ; you say that after Luke pointed it out , you changed your initial statement :

[pre]
Code: 
[valCDC] = cell.Offset(0, -8).Select
to :

[pre][code][valCDC] = cell.Offset(0, -8).Value
Now , you say the extraction works fine , but the saving is not correct ; have I understood you correctly ?


If so , just qualify the range value fully ; what is happening is that when you add a new workbook , Excel automatically makes it the active workbook ; so now , when you put in [valCDC] , Excel does not find a named range valCDC in the newly added workbook.


Replace the portion valCDC.Value in the following statement :

ActiveWorkbook.SaveAs Filename:=currpath & valCDC.Value & Format(Now, "dmmmyyy-hhmmss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
[/pre]
as follows :

ActiveWorkbook.SaveAs Filename:=currpath & ['Original Workbook.xlsm'!valCDC] & Format(Now, "dmmmyyy-hhmmss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False[/code][/pre]
Narayan
 
Narayank991, can you believe me one thing.......


You are awesome and a such geek, problem can't stands infront of you. I am very much happy with your explanation and i will keep it in my mind. Since last few days i am scraching my head to resolve this, as i am not so much proficient in VBA, still in learing stage in VBA classes in chandoo.


You have solved my problem. thanks a lot


Regards

Vijay
 
You must log in or register to reply here.
Back
Top