Re: Differcenes in two data ranges??
Sorry, I should have been more clear in my original post. You are correct when assuming that A=AA, B=AB, C=AC....etc. Except that the rows might not match up..so this is possible, A1=AA23, B1=AB23, C1=AC23,...etc. If you like I can upload a sample of my data sets.....Thanks again for the reply!!!
Dustin
What is the easiest way to find the differences in two different data ranges.
Each range consists of 7 columns, and each column must match all 7 columns in the other data range, and if not I would like to set the differences apart by either highlighting them or cut/paste them to another part of the sheet. I have attempted to use the Advanced autofilter, but this just either filters out everything or nothing.......Any help on this, would be greatly appreciated.
Re: Adding line to 3D bar graph??
haha...thats kinda what i expected...but i thought i would check with the big guns....
Thanks
Dustin
is it possible to add a horizontal line to a 3d bar graph??
Re: office2003 slower??
Jack....I was able to figure out what was slowing down the calculations...if i turned my calculations from automatic to manual in certain parts of my code, it speed up the deleting of the rows. Thanks for your help!!!
Dustin
Re: office2003 slower??
Hello Jack,
I now know what you mean when you say functions should be used to return a value(s). I guess I was just using the functions return value, more of an error check, to verify the function was completed. I have since changed that function to a sub procedure...and that actually sped up the filtering process by a little!!
As far as the upgrade of office2003, I am not sure how our IT dept. did it, I was prompted to accecpt the upgrade and re-boot. I have contacted our IT dept. and inquired to see if anyone was having similiar problems, and they have not heard of any yet......I'm thinking maybe it is internal to excel, maybe they have a differnet algorigthm for deleting/renumbering rows/cols.....not sure???
I'm not sure what part of the code you cannot see...but let me know, and I will post it.
p.s. thanks for the data type tip... int -> long
Thanks again for your help!!!
Dustin
Re: office2003 slower??
Hello Jack,
I understand that functions do not take actions, but they do accept arguments....which is what FVar is. As far as saying my code needs better construction....I'm not claiming to be a programming guru, but its beyond me how you can state that my code needs better construction, by just seeing a few lines. I don't mean to come across as being defensive, but you have no idea how this function is being implemented....
I do agree that my code is'nt near as efficient as it could be, but thats why i'm in here trying to learn from helpful guys like you!!
Does anyone find that Excel2003 runs slower than excel97. My company has just upgraded excel97 to excel2003 and I am finding that one of my macros that delete mulitiple rows is running alot slower than it did in excel97.
Function Filter(FVar)
'==============================================================================================================
' Local Variables
Dim rnData As Range 'stores range of used data
Dim irows As Integer 'stores number of used rows
Dim icolumns As Integer 'stores column number where it sorts through the True & False. (Always Col F or #6)
'==============================================================================================================
Application.ScreenUpdating = False
Range("A1").Select
Set rnData = ActiveSheet.UsedRange 'stores range of used data
Let irows = rnData.CurrentRegion.Rows.Count 'number of used rows
Let icolumns = 6
If Range("A2") = Empty Then
GoTo Finish
End If
Cells(1, icolumns).FormulaR1C1 = "Sort" 'Labels the heading of Col F to Sort
Cells(2, icolumns).Select 'Selects next row down ("F2")
Selection.FormulaR1C1 = "=OR(RC[-3]={""" & FVar & """})" 'This formula checks to see if ColC "Stations" EQUALS any of
'the Filter Criteria stored in FVAR. If so, it sets ColF to True,
'if not, it sets it to false.
Selection.Copy Destination:=Range(Cells(3, icolumns), Cells(irows, icolumns)) 'Copies same formula as above, to all other USED rows.
Set rnData = ActiveSheet.UsedRange 'Stores range of used data in rnData again, just in case there is no more data
Selection.AutoFilter 'Applies autofilter to all the headings.
With rnData 'Filters out all the Data that have ColF equal False.
.AutoFilter Field:=6, Criteria1:="False"
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Deletes entire row of any data that has ColF=False.
.AutoFilter 'Shuts off autofilter
End With
rnData.Columns(icolumns).Delete 'Deletes ColF, gets rid of True/False
Application.ScreenUpdating = True
Range("A2").Select
Finish:
Counter 'Updates counter on status bar, of how many events are left
End FunctionThe " .entirerow.delete" is the command that is taking all the time. Does anyone have any suggestions on how to get around this?? I would like to just use autofilter, but autofilter only lets me setup two different criteria, I need around 20 different criteria.
Re: Printing multiple ranges w/o spaces
that did it!! thanks a ton!!
I have searched the forums, and have not been able to find a way to print 2 ranges, but I want to print them side by side. I am using the "Freeze Pane" to freeze the columns of months in column A.
This is what I have been using so far:
Dim PrintRange as Range
Set PrintRange = Sheets("Yearly Data").Range("A1:A18, AM1:AZ18")
PrintRange.PrintOut Copies:=1, Preview:=True, Collate:=TrueThis prints both ranges at the same time, but on two separte sheets. If anyone has any suggestions, it would be greatly appreciated...
Re: Automatic update of data
that is great....im just not too sure where to place this code in order for it to initialize the timer. I have tried putting it in its own module, and I have also tried putting it under "ThisWorkbook". It seems to me the timer is never being activated??
hello...is it possible to develop a way to automatically update/import data into my excel based program, from a text file every:
1) Hour or
2) Every time the text file is updated
If anyone is able to help me with this...it would be greatly appreciated!!!
Regards
Re: Control source of textbox
thats what the problem was!! thanks again!!
Re: Control source of textbox
check out BtnSPS1_Click() routine under userform1. When I push any of the buttons on userform1, I was looking for userform2 to pop up and display the correct text......see what you think, i guess
Re: Control source of textbox
still gives me problems using that code...
giving me a runtime error-"Object Required"
Who knew it would be this tough!!
I am trying to change the control source of a textbox...i have tried the following code, without any luck:
txtOperations.ControlSource = Sheets("Severity Data").Range("G6")
The contents of G6 is very long ( ~200-500 characters), should I maybe store the string of text in a file instead of a cell, then copy it to the text box.
If anyone has any suggestions, it would be greatly appreciated!!!
Regards,
Dustin
Re: Reading individual characters
Perfect...Thanks alot!!
Hello, I am trying to read in a text string located in a cell on my worksheet. This cell is formatted to contain text values, because I am inputting binary values ("0010").
My problem is that I need to read each character of the string, one by one. How is it possible for my to check each digit?
If anyone has any suggestions, it would be greatly appreciated!!!
Regards,
Dustin
Re: graphs on user form??
Thanks bud...that'll give me a good start
Dustin
Hello, would anyone know the best way of making a graph created in excel, appear on my user form?? The graph will be continually changing on a hourly basis, and I would like the graph on the user form also to update? Any suggestions or comments would be greatly appreciated!!
Dustin