highlight duplicate rows comparing two cells

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Programming
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Display CountA Formula and Text String in a cell I have the following line of code that inserts into a particular cell the count of the No. of asset records listed above it plus a text string declaring " Assets in the selected period" plus it takes the date value of two variables and formats them and places them in cell along the text rng.Formula = "=COUNTA(R[-4]C:R[-1]C)" & "&"" assets Aquired in the selected period " & "" & Format$(first, "short date") & "" - "" & Format$(last, "short date") My problem is that this does not display as i would have liked the dates are merely displayed as decimal numbers. I have changed the format of the cell to correct this but that does not appear to alter anything. How do i get the cell to display the count for the cells display the text message and the criteria range in the same cell Tag: highlight duplicate rows comparing two cells Tag: 968949
  - 2
    - Problem with Macro I am getting the following error message with this macro: Wrong number of arguments or invalid property assignment It works fine when I only reference 1 sheet but doesnt run when I reference 2 sheets. Sub DeleteRowIfZeroInA() Dim X As Long Dim R As Range Dim LastRow As Long With Worksheets("Sheet3", "Sheet4") LastRow = .Cells(Rows.Count, "A").End(xlUp).Row For X = LastRow To 1 Step -1 If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value <> "" Then .Cells(X, "A").EntireRow.Delete xlShiftUp End If Next End With End Sub -- Thanks Bob Tag: highlight duplicate rows comparing two cells Tag: 968947
  - 3
    - Copying a cell value to a specific cells Hi, I am looping through a range of cells looking for certain criteria. When one is reached, I want to copy the value into the next 10 cells in the same row as the active cell in the loop. I am trying to use the offset property to do this but cannot get it to work. Is this the right way to approach this? kind regards, Matt Tag: highlight duplicate rows comparing two cells Tag: 968940
  - 4
    - Code runs to slow We have a spreadsheet into which we dump a large amount of data (averaging around 55,000+ rows). We then use formulas to detect error in the data so that we can go back into the original program and correct those errors. What the code below does (my thanks to Rick Rothstein, MVP -VB) is hides all rows which do not have errors leaving only the rows with errors (saves one from having to scroll through more than 55,000 rows in search of errors). My problem is that it takes to long to hide the rows (in excess of 5 minutes). Does anyone have any thoughts on how to speed up the process? Sub HideRowIfZeroInG() ' ' Application.ScreenUpdating = False Dim R As Range Dim LastRow As Long With Worksheets("Negative Miles and Missing Perf") LastRow = .Cells(Rows.Count, "J").End(xlUp).Row If LastRow > 65536 Then LastRow = 65536 For Each R In .Range("J3:J" & CStr(LastRow)) If R.Value = 0 And R.Value <> "" Then R.EntireRow.Hidden = True Next End With Application.ScreenUpdating = True End Sub Tag: highlight duplicate rows comparing two cells Tag: 968937
  - 5
    - Combining Fors and Ifs Is it possible to combine the following :- For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10") If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString) Or _ (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString) Then MsgBox "xxxxxxxxx" Exit Sub End If Next MyCell For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16") If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString) Or _ (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString) Then MsgBox "yyyyyyyyyyy" Exit Sub End If Next MyCell into something along the lines of :- If For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10") If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString) Or _ (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString) End If Next MyCell Or For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16") If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString) Or _ (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString) End If Next MyCell Then "zzzzzzzzzzzzzzzz" Thanks in advance Sandy Tag: highlight duplicate rows comparing two cells Tag: 968935
  - 6
    - problem moving columns around All, I need to re-order some columns in Worksheet. Here is the code which works just fine (to a point): ..... For fldCnt = 0 To numOfFlds - 1 ' Check every name in the first row ' For colNum = 1 To numOfCols fldVal = wks.Cells(rowFirst, colNum).Value If fldList(fldCnt) = fldVal Then If colNum <> (found + 1) Then ' Column needs to be moved to 'curPos' ' Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum), wks.Cells(rowLast, colNum)) Set rngDst = wks.Range(wks.Cells(rowFirst, curPos), wks.Cells(rowLast, curPos)) Range(rngColToMove.Address).Select Selection.Cut Range(rngDst.Address).Select Selection.Insert (xlShiftToRight) curPos = curPos + 1 End If found = found + 1 Exit For End If Next colNum Next fldCnt ..... The problem is that when I move the column the contents of "wks.Cells(rowFirst, colNum)" does not change to reflect the column's movement.. In other words, if the original column order is a,b,c,d and I change it to d,a,b,c what I see in the first row, programmatically, is still a,b,c,d. It is as though I need some sore of "refresh" after I do the cut/paste. I tried making it a Range object and then resetting it after the cut/paste, but the order was still a,b,c,d. Any ideas? TIA, Bill Tag: highlight duplicate rows comparing two cells Tag: 968933
  - 7
    - Deleting a row that has a zero in column A I would like to know how I would build a macro that deletes an entire row that has a zero in column A. For example, my column A has values greater than 0 until the end of the report and then the values are all be zeros: 16 10 8 7 5 0 0 0 I would like a macro that deletes the entire row that has a zero in column A. Thanks. Bob -- Bob Tag: highlight duplicate rows comparing two cells Tag: 968923
  - 8
    - Updating from one list to anoter Hi I have a bit of a prob, I have one excel workbook with a data list on it, and another workbook with another datalist on. every piece of data in column a workbook1 appears in colum a of book2, I need to find the position of this data in column a from workbook 2 and copy columns d, f, e of the first book to the columns of d, f, e of the second book. any ideas, I am doing a loop at the moment but its taken ages and would like to speed it up by not activating the second workbook, any ideas would be great John Tag: highlight duplicate rows comparing two cells Tag: 968921
  - 9
    - IF sum help I am trying to add more than one IF and cannot for love nor money, One sheet called positions where i enter details of positions placed. i will enter 1 in a cell i want it to be 150 in another, if i enter 2 i want it to be 100 etc Hope you can help, k1ngy Tag: highlight duplicate rows comparing two cells Tag: 968919
  - 10
    - If condition Is there a way to have this condition read in this one line. IF cells(valid,13)>"" and (cells(valid,19)>""and ((cells(valid,19)-cells(valid,13))< 20 then cells(valid,30)=cells(valid,8)&CEDissue I am sure I am not following the correct syntax for the nested if but I am having a hard time finding the correct syntax in this case. Thanks Tag: highlight duplicate rows comparing two cells Tag: 968909
  - 11
    - Syntax problem with code Below is a macro I am testing. I am trying to create 26 command buttons, starting with Commandbutton6, but a caption starting with number 1. Something is wrong with the "Activesheet.OLEObject" line and I don't know what. Private Sub CommandButton2_Click() Dim h, w, t, l As Long Dim k As Integer h = 21 w = 91.5 l = 50 t = 92 For k = 1 To 26 ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton." & k + 5, Left = l, Top = t, Width = w, Height = h) .Object.Caption = "Button " & k + 5 t = t + 38 If k = 7 Or k = 14 Or k = 20 Then l = l + 145 t = 92 End If Next k End Sub Can someone tell me what is wrong? Thanks One other question. I have seen the syntax similar to what I show below. If using this syntax, what is "newbutton" DIM as? Set newbutton = Applicationsheet.OLEObject.Add(Forms.CommandButton....) Thanks, Les Tag: highlight duplicate rows comparing two cells Tag: 968900
  - 12
    - Does the folder exist? Excel XP & Win XP How can I determine if a specific folder, say "C:\The Folder", exists? I have an OP who will be sending some files, and the code to place the files in that folder, to some people. Thanks for your time. Otto Tag: highlight duplicate rows comparing two cells Tag: 968898
  - 13
    - Programatically click CommandButton The following expression works fine and clicks CommandButton #1:- Call UserForm1.CommandButton1_Click Is it possible to assign a variable to the number of the button clicked, along the lines:- For X=1 to 5 step 1 Call UserForm1.CommandButton(X)_Click (Then does what clicking the CB does) Next X This doesn't work, but I don't know if the fault is incorrect syntax, or that the expression simply cannot take a variable. donwb Tag: highlight duplicate rows comparing two cells Tag: 968897
  - 14
    - Excel Custom Box I am running Microsoft Office Enterprise 2007 on Window Vista Home Premium. When I go to the developer tab, I go to insert and then choose the "More Options" option. I choose "Shockwave Flash Object" and make an object. I click on properties and then can't find a "Custom" field. It works on another computer with Microsoft Office 2007 installed on it, but I don't have what I need on that computer. Can anyone tell me how to get the "Custom" field back, or what the values in the "Custom" field correspond to with the other fields? Tag: highlight duplicate rows comparing two cells Tag: 968896
  - 15
    - COPY ROW I am testing to see if the value on in a row is true and If it is true I would like to paste the row to the next sheet on the first available blank line. For DLV = 2 to 1000 For DLV2 = 2 to 1000 If cells(dlv,23) >"" and also cells(dlv,8) <> !DLV CONTAINERS cells(dlv2,8) then copy this row to sheet DLV CONTAINERS. I am also not sure if I am referencing the sheet correctly in the if statement. Thanks Robert Tag: highlight duplicate rows comparing two cells Tag: 968894
  - 16
    - Macro to create new workbook I have a scheduling workbook template. In this template workbook I need the code to accomplish the following: 1) protect the template workbook so that it can never be changed, 2) code to create a new workbook via copying the template workbook. Thanks, Les Tag: highlight duplicate rows comparing two cells Tag: 968872
  - 17
    - Manipulating Time I need convert time 10:20:24 into minutes so that I can divided a number to determine how many units are being done a minute. I don't know how to do this. Please help Thanks Tag: highlight duplicate rows comparing two cells Tag: 968871
  - 18
    - Screen Flashing I have the following code when a worksheet is started and the exited: Private Sub Worksheet_Activate() Dim cst As String Dim xcell As Range Dim ycell As Range unprotectsheet stopautocalc cst = Sheets("Databases").Range("currentcost") Set xcell = Range(cst & "tlr") Set ycell = Range(cst & "brr").Offset(4, 2) Range(xcell, ycell).Select Selection.EntireRow.Hidden = False HideAroundSelection Application.Goto Reference:=Worksheets("Cost Summary").Range("A1"), _ Scroll:=True Range("a1").Select startautocalc protectsheet End Sub Private Sub Worksheet_Deactivate() Unprotect stopautocalc Range("A:A").EntireRow.Hidden = False startautocalc protectsheet End Sub Sub stopautocalc() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With End Sub Sub startautocalc() With Application .ScreenUpdating = True .EnableEvents = True .Calculation = xlCalculationAutomatic End With End Sub I would like to make it so that the screen doesn't flash when it was started and exited. I thought the 'stopautocalc' would work, but it doesn't. Any ideas on how to stop it from flashing? Thanks Tag: highlight duplicate rows comparing two cells Tag: 968867
  - 19
    - Curious about macro possibility I was wondering, and probably reaching for this one, if a macro can write another macro and place it in the appropriate "Microsoft Excel Object" sheet? The project I am working on is a annual scheduling workbook that will create 26 sheets (payperiods) when a commandbutton is clicked and name each sheet accordingly. During this execution, I was wondering if I could also: 1) create 26 commandbuttons with same caption as its respective sheet, size and align each button on a sheet, 2) write the three line code for each commandbutton, using the caption (also name of sheet) as the reference to GOTO that sheet? Just curious about number 2. Thanks, Les Tag: highlight duplicate rows comparing two cells Tag: 968862
  - 20
    - Pivot table I have a pivot and I need to refresh data every same time, but I haven't to select the refresh every check box, because it's hide Tag: highlight duplicate rows comparing two cells Tag: 968860
  - 21
    - Selection based on partial input Hi to all, In an Excel workbook, sheet1 has a list of the customer names : col1=idx and col2=name I need to build up a user form with one Textbox one Listbox one Button (Exit) I need to enable the user to input the beginning of a name in the textbox, and, as each new typed character comes in, I want the listbox to display (in full) all of the names on sheet1 beginning with the actual content of the textbox to be listed in the listbox. The final selection is then done by a double click on the choosen name of the listbox. How can I code that in vba ?? Thanks by advance for your help, Herve+ -- Herve Hanuise http://www.affordsol.be Tag: highlight duplicate rows comparing two cells Tag: 968858
  - 22
    - What wrong with defined variable? Does anyone have any suggestions what wrong is the variable - Mywebsite on following coding? The url cannot be assigned into Mywebsite variable. Does anyone have any suggestions? Thank you very much for any suggestions Eric Dim Mywebsite As String Set Mywebsite = "http://www.stata.com/help.cgi?macro" With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Tag: highlight duplicate rows comparing two cells Tag: 968857
  - 23
    - find name of a chart I am writing some vba code in Excel 2003 to use the histogram function to make histograms of data in a number of different worksheets. I would like to delete all existing charts in a worksheet and then create a histogram and then modify its properties. I have: Application.Run "ATPVBAEN.XLA!Histogram",ActiveSheet.Range("$D$2:$D$733") _ , ActiveSheet.Range("$K$2"), ActiveSheet.Range("$J$3:$J$21"), False, False _ , True, False ActiveSheet.ChartObjects("Chart 1").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 1").ScaleHeight 2.98, msoFalse, msoScaleFromTopLeft ActiveChart.Legend.Select Selection.Delete If there is an existing chart in the worksheet, this fails at ActiveSheet.ChartObjects("Chart 1").Activate so I would like to know how to get the name of the chart created by the histogram. Could anyone help please? Thanks Chris Tag: highlight duplicate rows comparing two cells Tag: 968846
  - 24
    - Help on macro coding fo URL Refering to the post under Excel Worksheet Functions Does anyone have any suggestions on following macro coding? [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.stata.com/help.cgi?macro", Destination:=Sheets("Temp").Range("$A$1")) [Not working] myVariable = "http://www.stata.com/help.cgi?macro" With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & myVariable, _ Destination:=sheets("Temp").Range("A1")) It seems to me that "URL;" & myVariable is not equal to "URL;http://www.stata.com/help.cgi?macro". Do anyone have any suggestions on how to solve it? Thank you very much for any suggestions Eric Tag: highlight duplicate rows comparing two cells Tag: 968845
  - 25
    - Reference cells in a Hidden Worksheet for display in a UserForm I have workbook which has a number of reference worksheets that need to be hidden from the users view. Those sheets need to be referred to by a UserForm as part of drop down lists and other validations. When trying to populate the combo box I get the following error.... Run-time error '1004': Select method of Range class failed Refer to the routine that is trying to create the list below Public Sub SetAllLists() 'This routine will set all lists with the current values that are in place at that time 'Create the Building Drop Down list for the Inspection form Worksheets("Reference").Activate Worksheets("Reference").Range("ReferenceBuildingHeadingStart").Select If ActiveCell.Offset(1, 0).Value = "" Then ActiveCell.Offset(1, 0).Select 'If there is only one entry in the List box then no need to sort or do multiple selection ElseIf ActiveCell.Offset(2, 0).Value = "" Then ActiveCell.Offset(1, 0).Select 'If more than one entry then use the sort to refresh the list Else ActiveCell.Offset(1, 0).Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("ReferenceBuildingHeadingStart"), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End If 'Now reset the List of details shown on the page Inspection.frmBuildingComboBox.RowSource = ActiveWindow.RangeSelection.Address End Sub Is there any way to refer to data to worksheets that are hidden without getting this error ? Tag: highlight duplicate rows comparing two cells Tag: 968833
- Next
  - 1
    - Merge Data From one Workbook to another I have two work books. One is "historical" data and the other is "update" data for records in the first. I want to program some vb to update "historical" with the "update" data. Also I would not be using all the data in the whole row. I will only be updating certain columns of a row to historical: Column A in both is the account number. The script should take each account number in "update" and match it to the account number in historical, if there is a match it should update columns B, E, M, O of historical with update. If there is no match it should add a new record and update the same columns. Also this code should be contained in historical, also I need a way to activate rows in historical to show active records, what I mean by active is only records that were updated or added should be active. In other words maybe add another column called active to historical and as the script runs it puts the word active in a column or deactive in the same column and then I could filter it to show only the active ones. Im not sure if there is a better way of doing that or not, but i do not know how to code my thought either. Thanks so much in advance. Tag: highlight duplicate rows comparing two cells Tag: 968832
  - 2
    - Range Objects + Names + Error 1004 I seem to be running into the 1004 error on what I thought would be something simple. I have created 4 names one of which is "Clothing", it refers to cells B4 to B8. I want to simply place the arrData into each of the five cells. I can't get past that line setting the range object. I thought setting range objects was easy. The range is already named what is causing this error? A piece of the offending code follows: Is there some "rule of thumb" to follow when setting and using range objects, I always seem to be getting thrown with them. Thanks David. Public Sub ApplyData() Dim i As Integer Dim j As Integer Dim rng As Range Sheets("Master").Activate Set rng = Range("Clothing") ' Can't get past this line rng.Cells(1) = arrData(0, 0) rng.Cells(2) = arrData(0, 1) rng.Cells(3) = arrData(0, 2) rng.Cells(4) = arrData(0, 3) rng.Cells(5) = arrData(0, 4) . . . End sub As Always Much appreciated! Tag: highlight duplicate rows comparing two cells Tag: 968826
  - 3
    - Convert Time Column to Text I have a spreadsheet that contains Artist, Song and Length of Song info that was created by a music collection software. EXAMPLE CELLS 1 shows the cell's actual values and EXAMPLE CELLS 2 shows how it looks. I'm looking for a way to actually convert all of the song length values in Column C so they are text data type and look exactly like Column C values in EXAMPLE CELLS 2. So basically, Song 1 in cell C1 would be 03:50 and would mean 3 minutes and 50 seconds. Is there a way to do this witha formula or does this require vba code? ** EXAMPLE CELLS 1 *************** A B C ----------------------------------------------- 1 Artist1 Song1 12:03:50 AM 2 Artist2 Song2 12:03:42 AM 3 Artist3 Song3 12:04:02 AM ** EXAMPLE CELLS *************** A B C ----------------------------------------------- 1 Artist1 Song1 03:50 2 Artist2 Song2 03:42 3 Artist3 Song3 04:02 Tag: highlight duplicate rows comparing two cells Tag: 968824
  - 4
    - Limit open dialog to certain directory I'm trying to understand how to use the open windows dialog command to open a certain directory - BUT limit the user to only selecting files to open out of that directory. Possible? Tag: highlight duplicate rows comparing two cells Tag: 968823
  - 5
    - Pass a variable to a different sub I have a 2 command buttons on a userform. cmbCalculate and cmbAddToQuote. cmbCalculate has code that confirms all textboxes have data before calculating a price. cmbAddToQuote adds the price to a worksheet. How can I pass a variable value from cmbCalculate code to cmbAddToQuote code. Here is what I have for an example. I want to stop Sub cmbAddToQuote_Click() if the variable StopCode = True. IS this possible? Public Sub cmbCalculate_Click() Dim StopCode as Boolean If textbox1 = "" Then MsgBox "Try Again." StopCode = True Exit Sub End If End Sub Private Sub cmbAddToQuote_Click() Call cmbCalculate_Click If StopCode = True Then Exit Sub '.....additional code End Sub Thanks in Advance, Ryan Tag: highlight duplicate rows comparing two cells Tag: 968815
  - 6
    - Check Data Validation status via a macro Since I cannot run a macro to query cell(s) for Conditional Formatting results, can I query for Data Validation results? I have 40+ sheets in my workbook and I would like to set the Sheet Tab colors to indicate whether a sheet has out of limit data in any cell on the sheet. Thank you for your help. Tag: highlight duplicate rows comparing two cells Tag: 968814
  - 7
    - Is the array empty? Hi I am using a macro to load an array with data from excel. This is time series data. As I loop through all the years and months, there are some months that don't exist in a specific year (eg for 2005, the data starts from April, whereas my array loops over all months). So when I look up this array in the local variables, each element of the array shows up as EMPTY. this causes a problem cos at the end of all this, I'm trying to do : worksheetfunction.average(array) and it fails when I the array is empty. Is there anyway to determine if the array is empty, cos then I can use an if statement to circumvent. I've tried using lbound, ubound, isempty etc etc...nothing seems to be working. Suggestions are deeply appreciated. Thanks Chet Tag: highlight duplicate rows comparing two cells Tag: 968812
  - 8
    - Copy from on eworkbook to another Hi, I have two columns which copy to the new book rounded to the nearest dollar. None of the other columns are doing this. It should be to 2 decimal places. CAMDepMade is long Sub NuSaveCAMDepMade(TenRow, CAMDepMade) 'Created 3/13/2008 by Karen Hoagland Debug.Print "Starting NuSaveCAMDepMade " & Application.ScreenUpdating With MainPagepg 'save CAM Deposits Made With .Cells(TenRow, 14) If CAMDepMade = 0 Then .HorizontalAlignment = xlRight .WrapText = False .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" .Value = CAMDepMade End If If .Value <> CAMDepMade Then .HorizontalAlignment = xlRight .WrapText = False .NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" .Value = CAMDepMade End If End With End With End Sub -- Thanks for your help. Karen53 Tag: highlight duplicate rows comparing two cells Tag: 968809
  - 9
    - Workbooks property For the life of me I can not remember how to add a variable name to a file path. I have converted a file from text to xls and have saved it as myfile - date in mmddyy I have tried workbooks("myfile-"&date).close savechanges:=false. but it does not like that. Tag: highlight duplicate rows comparing two cells Tag: 968793
  - 10
    - array dimensioning In C++ one can expand or decrease the dimension an array. Can the same thing be done in VBA? -- Thanks for your help Tag: highlight duplicate rows comparing two cells Tag: 968792
  - 11
    - Detecting a change in a cell Hi all, I'm fairly new to VBA programming. I'm using Excel 2003. I need to know when a particular cell (it's actually a single cell "range" called MYRANGE) has changed. When I detect the change, I need to see if the new numeric value in that cell is not zero; if it's non-zero then I need to activate some code. Unfortunately that cell is changed via a formula - so using Worksheet_Change is apparently not going to work. That is, Worksheet_Change doesn't get triggered for cells of this type. I tried using .OnTimer to repeatedly check the value of that cell, but I couldn't get it to work very well. It seems like there has got to be an easy way to trigger some code to activate when a cell has changed (even for a cell that changes via a formula). Thanks for any help! -Brad Tag: highlight duplicate rows comparing two cells Tag: 968775
  - 12
    - Do while loop I am trying to excercise a loop. I have a series of copy and paste commands that I want to exercise if cells(i,1) is any of 9 text values (Cos, DL, MRB, Swap, Finishing, DST2,Fin-T,Pre-T,Trap). Is there an easy way to do those either than 9 "IF" statements? Thanks, Mike. Tag: highlight duplicate rows comparing two cells Tag: 968772
  - 13
    - How to know if cell has name How can I test a cell for a Name, "activecell.name". In my code below, it will generate an error if a cell is clicked that has no ".Name". (Option Explicit assumed) '====== Sub x() 'Begin Registry setting 'Notes: '100% active on all cells clicked sSpecLastWs = ActiveSheet.Name SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _ Key:="sSpecLastWs", setting:=sSpecLastWs sSpecLastCell = ActiveCell.Name 'FAILS HERE SaveSetting appname:="ADM_Spec_Index", section:="WorkSheetNames", _ Key:="sSpecLastCell", setting:=sSpecLastCell 'End Registry setting End Sub '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 Tag: highlight duplicate rows comparing two cells Tag: 968771
  - 14
    - Return cell(s) Conditional Formatting Status via a macro Using a macro, I would like to read the Conditional Formatting status of cells so that I can, for example: color a sheets tab red if any cells on the sheet are out of range based on their conditional format status. Attached is code that I thought would do the trick. It works if I manually set the cells to colors 3 or 45, but always returns -4142 if cell colors are set via Conditional Formatting. Sub Sheet1_ColorCellsTest() Dim redcells As Integer Dim orangecells As Integer redcells = 0 orangecells = 0 For Each c In Worksheets("Sheet1").Range("D7,E12:E44").Cells Select Case c.FormatConditions(1) Case 3 '3 red redcells = redcells + 1 Case 45 '45 orange orangecells = orangecells + 1 Case Else 'Not red or orange redcells = redcells orangecells = orangecells End Select Next If redcells > 0 Then Sheets("Sheet1").Tab.ColorIndex = 3 'If any red, make tab red 3, done Else If orangecells > 0 Then Sheets("Sheet1").Tab.ColorIndex = 45 'Or if any orange, make tabe orange 45, done Else Sheets("Sheet1").Tab.ColorIndex = 50 'Else if any No color, make tab green 50, done End If End If End Sub Tag: highlight duplicate rows comparing two cells Tag: 968770
  - 15
    - Excel Macro Help I need to process an Excel file. I want to run a macro. The macro will not know how many rows are in the file. The column A has a date time stamp for each row. Column E has the value N, L, or R. The rows will start with Ns. Than the rows will have L or Rs. The last rows will have Ns. When the rows start to have L or Rs. I need to grab the date time and when the N begin again I need the date time so I can calculate the total time and add up the number of L and Rs. Here is a short example. 1/1/2008 10:10 N 1/1/2008 10:11 N 1/1/2008 10:12 N 1/1/2008 10:13 L 1/1/2008 10:14 R 1/1/2008 10:15 L 1/1/2008 10:16 N 1/1/2008 10:17 N Total there are 2 L and 1 R and time is 4 min. If this is not the right place for this please direct me to the correct place. Point me in the right direction to find help. Thank you for your assistance. Tag: highlight duplicate rows comparing two cells Tag: 968769
  - 16
    - Pass a Variable from one sub to another sub I have two command buttons in a userform; cmbCalculate & cmbAddToQuote. cmbCalculate when clicked calculates a price. cmbAddToQuote adds the price to the worksheet. In order to calculate a price the user must enter all valid information into the userform, if not a message box prompts them what to do and the sub exits. My problem is what if the user clicks the cmbAddToQuote button before a price is generated. I want to calculate the price and exit the entire sub if not all information is entered. But this code does not work why? Public Sub cmbCalculate_Click() Dim ExitEntireSub as Boolean If textbox1 = "" Then MsgBox "You must enter Information in TextBox1." ExitEntireSub = True Exit Sub End If End Sub Private Sub cmbAddToQuote_Click() Call cmbCalculate_Click If ExitEntireSub = True Then Exit Sub 'code to add information onto worksheet End Sub Tag: highlight duplicate rows comparing two cells Tag: 968756
  - 17
    - Drop Down Menu Options? Need a PRO Big thanks to groups user huntermcg for this find. I have entered this into my "ThisWorkbook" - Private Sub Workbook_Open() Dim sh As Object On Error Resume Next Application.CommandBars("Navigate XL-Dennis").Delete Application.CommandBars("Navigate Sheets").Delete On Error GoTo 0 With Application.CommandBars.Add("Navigate Sheets", , False, True) With .Controls.Add(msoControlButton) .TooltipText = "Move Back" .FaceId = 1017 .OnAction = "Move_Back" .BeginGroup = True End With With .Controls.Add(msoControlDropdown) For Each sh In ThisWorkbook.Sheets .AddItem sh.Name Next sh .TooltipText = "SheetNavigate" .OnAction = "Sheet_Navigate" End With With .Controls.Add(msoControlButton) .TooltipText = "Move next" .FaceId = 1018 .OnAction = "Move_Next" End With .Protection = msoBarNoCustomize .Position = msoBarFloating .Visible = True End With End Sub and then I entered this into a module - Private Sub Sheet_Navigate() Dim stActiveSheet As String With CommandBars.ActionControl stActiveSheet = .List(.ListIndex) ThisWorkbook.Sheets(stActiveSheet).Activate End With End Sub Private Sub Move_Back() On Error Resume Next ActiveSheet.Previous.Select End Sub Private Sub Move_Next() On Error Resume Next ActiveSheet.Next.Select End Sub This created a neat little drop down menu for sheet navigation. (thanks to Bob Phillips for the code at http://www.excelforum.com/newreply.php?do=newreply&noquote=1&p=1358771) QUESTION - I've applied this to a project of mine and it works great. Although with my project I have a workbook with over 250 sheets :-) Is their a way to limit the amount of sheets displayed in the drop down box? also if the above is possible... Is their a way to have different set of sheets displayed for each worksheet? For example, on "sheet 1", the drop down only displays sheets 2-5. Upon the users arrival to sheet 4, the drop down menu displays sheets 30-50. Thanks for the help in advance. Tag: highlight duplicate rows comparing two cells Tag: 968752
  - 18
    - Close X button on Usser Form Hi Gentleman. I have a form that has the blue title bar along with the "X" in the upper right corner. I am having trouble finding the code or process of eliminating the "X" so the user will have only once choice to close the form through the Close button created in the form. Any suggestions would be highly appriciated to get be through this hurdle. Thanks! -- SBR Tag: highlight duplicate rows comparing two cells Tag: 968750
  - 19
    - Select First Row of Active Column Hi, It's always funny how the most basic problems are the hardest to figure out. I need a macro to go to the first row of a colum when a user selects a cell below. For example, if I have selected cell D9, or D12, or D1000, I need it to go to cell D1. Or, if I have selected cell E9, E12, or E1000 I need it to go to E1. Thanks for your help, Derrick Tag: highlight duplicate rows comparing two cells Tag: 968747
  - 20
    - Sumif with multiple columns in sum_range Hello, I have a set of data that has a label in column A the sales in column B, C & D: Column A Column B Column C Joel 500 677 Joel 575 752 Joel 650 827 Claudia 725 902 Tarzan 800 968 I'd like to run SUMIF where the "Range" is column A, the "Criteria" is Joel, and the "Sum_Range" is B:C but when I use SUMIF(A:A,"Joel",B:C) I only get the sum of column B which is 1,725. Can someone hook me up with a formula that can sum both column B & C in this situation. Thanks, in advance Daniel Tag: highlight duplicate rows comparing two cells Tag: 968745
  - 21
    - File URL Path I need help on how to get the URL path for an active file that is stored in an eRoom. I'm trying to generate a link to the file to include in an email. When I use the Visual Basic command ActiveWorkbook.Path I get C:\...\ApplicationData\eRoom\eRoomClient... What I want is https://e6.xxx.com/eRoom/extranet4/xxxx/0_1ab23 like you get when you right click the file on the eRoom page. Any ideas ? -- Thanks, Nancy Tag: highlight duplicate rows comparing two cells Tag: 968739
  - 22
    - Filename macro and a little more? The following works for me: Sub SaveAuto() Dim fname Dim fpath fpath = "C:\Documents and Settings\Ken\Desktop\" fname = Format(Now, "MDD_hhmm") ActiveWorkbook.SaveAs fpath & fname End Sub But in addition to the result of the date code, I would like the filename result to contain a prefix using the contents of a partcular cell of the original workbook (3 letters that represents a customer code) which would allow us to sort the files saved by customer in the saved directory. Can this be done Tag: highlight duplicate rows comparing two cells Tag: 968737
  - 23
    - If statement formulas I have two sheets in a workbook. One called Availability, and the other called schedule. The availability tells what time my employees are available to work and it look like this: A1 B1 C1 Name Start End Tom 6 AM 2 PM Jason 9 AM 5 PM John 1 PM 9 Pm Susan 8 AM 4 PM In the schedule sheet look similar to availability sheet accept it has one more column that will if I schedule an employee that will have a time conflict with availablity sheet. Look like this. A1 B1 C1 D1 E1 F1 Name Start End Jason 8AM 5 PM Not available before 9 am Susan 8 AM 6 PM Not Available after 4 pm John 6 Am 12 PM Only available from 1pm to 9 pm Tom 6 AM 2 PM Notice I added three more column. One tell a statement such as "Not Available Before", "Not Available After", and "Only Available From". The other two column tell if Column D has a statement such as "Not Available before", then column E specify the time that particular employee not available before what time. Similary, with "Not Available after" column E specify the time that particular employee not available after. Similary, if column D statement says the time is out of range or "Only Availbable From", specify the start time in E column and end time in F column. If time is within range then don't do anything or display blank. Notice the names of my employees in shedule sheet are not in order with the name of my employees in the availability sheet. I probably need vlookup formulas as well? What formulas do I put in cells of Column D, E, F the get the deserve effect from above? Thanks Tag: highlight duplicate rows comparing two cells Tag: 968713
  - 24
    - function for looking up text hi, don't know what's wrong with this function. it shall take the value of a cell and via select case look, whats in it and write into the active cell a certain text. Function PreTimeSlotsConv18(PreSlot18) Select Case PreSlot18 Case "Slot 1" PreTimeSlotsConv18 = "17:35" Case "Slot 2" PreTimeSlotsConv18 = "17:20" Case "Slot3" PreTimeSlotsConv18 = "17:05" End Select End Function cheers Tag: highlight duplicate rows comparing two cells Tag: 968712
  - 25
    - Programmatically Halt Macro and Open VB Editor I have an IF statement in a macro. If one branch is selected, I would like to halt (not end) macro execution and open up the VB editor window. What code would I use to accomplish this?...TIA, Ron Tag: highlight duplicate rows comparing two cells Tag: 968710

This post follows on from Dave Peterson's reply 13/3/08.
I replied twice on the 14th but they seem not to have posted.

Dave your code will be good if I want to just see unique values but
can I please get some help to highlight all rows where col C LastName
and col D FirstName are the same.
>I don't want to concatenate and advance filter as I want to be able to quickly search all records >when deciding which duplicate to delete or to keep both/all (other columns
>may differ).

I need to be able to use the code again so was thinking that it would
be put in a module in my personal.xls?

thanks again
Peta

Top

RE: highlight duplicate rows comparing two cells by Joel

Joel
Sun Mar 16 07:41:00 CDT 2008

RowCount = 1
Do While Range("C" & RowCount) <> ""
If Range("C" & RowCount) = Range("D" & RowCount) Then
Rows(RowCount).Interior.ColorIndex = 6
End If
RowCount = RowCount + 1
Loop
"pgcn@westnet.com.au" wrote:

> This post follows on from Dave Peterson's reply 13/3/08.
> I replied twice on the 14th but they seem not to have posted.
>
> Dave your code will be good if I want to just see unique values but
> can I please get some help to highlight all rows where col C LastName
> and col D FirstName are the same.
> >I don't want to concatenate and advance filter as I want to be able to quickly search all records >when deciding which duplicate to delete or to keep both/all (other columns
> >may differ).
>
> I need to be able to use the code again so was thinking that it would
> be put in a module in my personal.xls?
>
> thanks again
> Peta
>

Top