Posts by msb221

Re: Highlight used cells for pivot report selection

Thanks for trying, i just cant seem to get it to work. When it does work, ive had to change all the 'text to numbers' with that little diamond square that pops up.

The main problem is how the data is being copied i think. All the data is stored on a sheet named 'Data'. It is copied over using this formula..

Set pasterange = sht2.Range("C65536").End(xlUp).Offset(1, 0)
Selection.Copy Destination:=pasterange

The original data has no problems in how the numbers are stored, so no green triangles.

But after its been pasted into data2 for my operation the whole list has green triangles and thats why the pivot table isnt working, it displays nil values.

Is there a special paste thing i need to add onto that code because i cant figure this out.

Re: Highlight used cells for pivot report selection

Quote from msb221

Hey,
Thanks for the input. Unfortionatle it only worked once, but even then it was incorrect.

What happened was that all the products appeared but the numbers were zero all the way down, so it wasnt recognising them.

Also the part which seems to be causing trouble is the 'Range'

In break mode after i rolled my mouse over the range it appeared as 'Data2!C2:C3' thats it! When it should be something like B1 - CX

This is a real pain in the arse

Thanks,
Mark

Display More

Actually the code works fine, its just that my data has the usual 'Number stored as text problem'. The only trouble is that it doesnt go away unless i select the cells manually and click the little worning diamond and then 'convert text to number'.

I have tried macros that select the whole column and multiply the number by 1 to convert them but this only leaves a trail of zeros to cell 65555 or whatever.

aghhh

Re: Highlight used cells for pivot report selection

Hey,
Thanks for the input. Unfortionatle it only worked once, but even then it was incorrect.

What happened was that all the products appeared but the numbers were zero all the way down, so it wasnt recognising them.

Also the part which seems to be causing trouble is the 'Range'

In break mode after i rolled my mouse over the range it appeared as 'Data2!C2:C3' thats it! When it should be something like B1 - CX

This is a real pain in the arse

Thanks,
Mark

Hey,
This is an easy one for most of you.

Below is my code to create a pivot table. The data is located on a 2nd sheet named 'Data2' in columns B & C.

As you can see the range is already defined. How can i have this as a variable which holds the current address of the automatically selected - occupied cells in columns b-c???

So in order a macro that can..

1) select all occupied cells in colums b-c
2)paste this range as an address in d1
3) assign a variable which has the range for the pivot table macro to complete its work.

Thanks in advance :ufo:

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 05/05/2006 by markb
'




    Sheets("PivotReport").Select
    Range("B18").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Data2!R1C2:R27C3").CreatePivotTable TableDestination:= _
        "'PivotReport'!R18C2", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Products")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("QTY"), "Sum of QTY", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
End Sub

Re: VBA to AutoFilter list without zeros

Thanks for reading. Yeah thats right it only works on column P.

I dont need it to work on all columns because they contain other data that is irrelevant. If you look at the code notice that after the autofilter has been applied the computer selects the visable records, copies them and pastes into another sheet.

All i need is for the autofilter to work correctly so that everything else can do its job. I find it bizzasr that its not working correctly because the autofilter works fine manually.

:sheep:

Hi Guys,
I have a perculiar problem. Imagaine 'Sheet 1' is the master, all data is stored here. There is a combination of numbers and text in most fields and the cell formatting is set to 'Text'.

Now, all my existing macros work pefectly.

I am trying to filter all the records in the specified colum to hide rows that contain '0' zero. This is simple when i use the toolbar and apply the custom autofilter manually. However when i use VBA it does the complete opposite.

I have tried converting all the cell formats to 'General', 'Text', 'Number' you name it. Still failing.

I have also tried various other was such as inputting a qeustion mark instead of a zero, meaning that anything less than a charecter should be hidden.

Nothing.

Please take a look at a sample of my code below, and see if you can add any suggestions for correcting the Auto Filter feature.

Thankyou in advance

Sub ForecastofReturns()


Dim Pasterange As Range, sht1, sht2, sht3, sht4
Dim Msg As String
Dim cell As Range
Dim LastRow As Long
Dim criteria As String
Dim UpBound As Range
Dim LowBound As Range


Set sht1 = Sheets("data1")
Set sht2 = Sheets("data2")
Set sht3 = Sheets("data")
Set sht4 = Sheets("data3")


Application.ScreenUpdating = False
Sheets("data").Unprotect
Sheets("data").Select










'______ PRODUCT 1'
'______ Auto Filter'
'last row number of column to be filtered'
LastRow = sht3.Range("P65536").End(xlUp).Row
'Column to search down and filter out zeros'
sht3.Range("P1:P" & LastRow).AutoFilter Field:=1, Criteria1:="<>0"
'______ Select'
'Automatically Select Used range excluding header'
Set UpBound = sht3.Range("P2")
Set LowBound = sht3.Range("P2").End(xlDown)
Range(UpBound, LowBound).Select
'______ Copy'
Set Pasterange = sht2.Range("B65536").End(xlUp).Offset(1, 0)
Selection.Copy Destination:=Pasterange
'______ Deactivate the Auto filter'
sht3.Range("P1:P" & LastRow).AutoFilter

:drum:

Re: Autofilter Date Critera set Greater and Less than

Thank you so much that works a treat! : D

Hi Guys,
I want to apply an autofilter using VBA which sorts my data between dates.

The dates will vary so they have been declared as variables, which the user will be prompted to input via a message box.

The trouble is that the variables can be input into the autofilter code but it is then read as a plain date, it doesnt know that i want data greater than one date and less another.

When i add these symbols before my variables > < as seen below, it cannot interpret them. If i do this >"startdate" then it mistakes my variable as a text criteria.

How do i go about solving this problem, my brain is at wits end?

Sub DATESEARCH()
 
Dim LastRow As Long
Dim startdate As String
Dim enddate As String
Dim Pasterange As Range, sht3
Set sht1 = Sheets("data1")
Set sht2 = Sheets("data2")
Set sht3 = Sheets("data")




'last row number of column to be filtered'
LastRow = sht3.Range("c65536").End(xlUp).Row


'Input Start Date'
Msg = "Start Date - DD/MM/YY"""
startdate = InputBox(Msg)
If startdate = "" Then Exit Sub
    
'Input End Date'
Msg = "End Date - DD/MM/YY"""
enddate = InputBox(Msg)
If enddate = "" Then Exit Sub


Columns("c:c").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=>startdate, Operator:=xlAnd, _
Criteria2:=<enddate


End Sub

Thanks,
Mark :rambo:

Re: Finding records between dates

Hi,
Although i can see how it works, its doent work on my machine because it says that some form elements and stuff are missing.

Anyhow, ive adapted the code to have pop-up message box's instead.

The trouble is how do i write a variable into the autofilter code as a criteria?

It would work if there was no less than or greater than signs infromt, and there was 1 criteria. But there is two and i need to specify one greater and one less than.

Does anybody understand?

Here is my code so far.

Sub DateFilter()
Dim StartDate As String
Dim EndDate As String




Dim Pasterange As Range, sht1
Set sht1 = Sheets("sheet1")
Dim lastrow As Long
Dim Criteria As String
Dim Msg As String
Dim cell As Range
    
    Msg = "Start Date "
    StartDate = InputBox(Msg)
    If StartDate = "" Then Exit Sub
    
    Msg = "End Date "
    EndDate = InputBox(Msg)
    If EndDate = "" Then Exit Sub
    
    
'last row number of column to be filtered'
lastrow = sht1.Range("a65536").End(xlUp).Row


'Filter the data based on the passed date parameters
sht1.Range("A1:A" & lastrow).AutoFilter Field:=1, Criteria1:=>StartDate, Operator:=xlAnd, Criteria2:=<EndDate

please help asap

:yikes:

Mark

Re: Finding records between dates

Quote from grove

See if this gives you any ideas?

HTH

There used to be a zipped file here with the date filter. I downloaded it but there was no macro contained in the document?

Can you post it again with the macro in please :p

Mark!

Re: Count Total of no of records in a sheet

WOW! Thanks, such a simple but elegant solution.
Ok ive just hit my second challange, but once this is done im smokin!

When the auto filter is applied it still counts every record, how do i get it to count only the visable records?

Im sure there is an ease to this too, but i cant figure it out.

Mark x

:rambo:

Re: Count Total of no of records in a sheet

Hi guys,
This is great but instead of having a message box appear with the value, how do you paste it onto another sheet?

:rambo:

Hello people,

I desperately need some help regarding Excel 2002-2003 issues.

I created a macro driven user friendly spreadsheet in Excel 2002.
The problem is when i use it in 2003 i get the following error message constantly when i click a macro.

'Cant find project or library'

Here is a small example of the code for which this error occurs.

'Search and delete blank rows'


Sheets("data").Select


LastRow = ActiveSheet.UsedRange.Row - 1 + _
 ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = False
    For r = LastRow To 1 Step -1
        If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
    Next r

It highlights 'Lastrow' but i see nothing wrong with that command or my code. The same thing happens to various parts of perfectly functioning code all through the spreadsheet.

Heres the bizzar part. It opens and runs fine on half the company network computers with Excel 2003, while the other half with Excel 2003 it stuffs up. All the computers have exactly the same verison. Why should it stuff up for some and not the others?

Its very bizzar.

Please help as this is crucial to what im doing and will mean that months of work has gone down the pan. Then i will have to recommend macintosh to the IT department kos there is no reason to stay on pc. so prove me wrong. :rock:

Mark. :thanx:

Re: Finding records between dates

Yes i tried all that, it just doesnt work. I really dont know what im doing wrong

Hiya :rock:

I have data in cells A1 - D4.

I want Excel to Open Outlook and copy the contents of this selection into a new mail message ready for sending at the click of a macro!

Is this possible?!!!

So far it has been a hard task finding anything simple and coherent to understand.

Thanks, :thanx:

Hi Guys,
How do you search for records between dates? For example.

I have my dates stored in this format 10.10.05 - I want to be able to input dates in the same format and search between say 10.10.05 - 10.11.05.

How do you do this with a macro that when clicked displays a message box to input the 1st and 2nd date, then displays only records between those dates?

HOWEVER: There is a small problem. When you click on the cell displaying a dat like this - 10.10.05, it shows in the bar above as 10/10/05. Is this confusing the search?

Thankx in advance.

Mark B. :gift:

Hello Fellow Excellerz,

In my spreadsheet i have it designed like a form where by users input comment in the text box. However, when what you type is longer than the text box width it doesnt move onto the second line automatically.

How can i fix this problem?

Thanks,
:yikes:

Re: Find + Update

Hi roy, thats not what im looking for.

Im looking to seach my main sheet of data 'Data' by eg. ordernumber '123'

The whole row of data for order'123' is then copied & pasted into 'data2' from cells G2-Z2.

Those cells are linked to text boxes on another form, so the fields are shown in the text boxes. The user should modify the record from the text boxes, as the cells will change at the same time.

Then to finalise, the user hits another macro button to paste the newly updated row over the existing one in the main sheet 'Data'

Hopefully this makes sense.

Thanks

Hello there.
Please does anyone have an idea on how to Search for a record by number, eg 442. And replace it with another more upto date record (from another sheet) by copying the new data over the existing row, rather than adding it onto the bottom.

If anyone knows a macro to do this, i would be very grateful

Many thankx in advance!

Mark

Re: AUTOHIGHLIGHT cells and give reference

Oh yeah i forgot to mention that, well i overcome the problem by changing

MsgBox rngeAllCells.Address

by writing

MsgBox rngeconstants.Address

That gave me the selected cells range.
but now im gonna try the pasting technique! :thanx:

Thanks!!