Free Microsoft Excel 2013 Quick Reference

Use VBA to filter column for specific value

How to filter certain row when column contain certain value using vba? i try to manually add in filter using Excel, but it doesnt works, dont know why, anyone know how to write vba code to filter out rows of which some of its columns contain certain value?

Thanks


Post your answer or comment

comments powered by Disqus
Hello....I'm trying to create a macro that will scan a selected column for a value, and if it finds a cell with the value I am looking for, it will copy the entire row. For example, I am looking for cells with 3051 and H2.

1. 3051AABBSSH2
2. 3051SJDIUS
3. 3051SJHEISH3
4. 3051SJSJSH2
5. 3051SSJJJH2

After running the macro, it should have rows 1, 4 and 5 copied. I was trying to use the following code:

Public Sub seekAndFilter()
Dim rng As Range

For Each rng In Selection
If ((InStr(1, rng.Value, "3051") <> 0 And InStr(1, rng.Value, "H2") <> 0)) Then
rng.EntireRow.Copy
End If
Next
End Sub

The issue I run into is that it only will copy the last row that it finds with 3051 and H2, so in the above example, it copies 3051SSJJJH2 3 times. I am very new to making macros, so I'm sure it must be a dumb error I made. Any help would be much appreciated!

So, think your hot stuff.

Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem

I have the idea in my head but need a genius to help me with the fine print

Heres what i have so far (that doesn't work) but it gives an idea of what i want

Sub trucks()
Dim a(1000) As String

tonsum = 0

For i = 1 To 548

    a(i) = Cells(i + 1, 7).Value
    
    If a(i) = "SHVL1" Then
        Range(i + 1, 5).Select
        tonsum = tonsum + ActiveCell
    End If
     Cells(1, 9).Value = tonsum
    
Next i

End Sub
Hopefully someone konws what to do here..or has done it before
Thanks
Dan

Hi all,
I require a formula to search a column for specific data in excel.
Please see attached example which best illustrates what I am trying to achieve.
Thanks in advance

Hi All,

I am new to VBA and this forum, but have already learned a lot since joining!

I think there are a few ways to do what i'm trying to do and my first way may not be working. I want to copy/paste between different sheets based on a value in the row. For example, if I have these rows:

Company New/Exist Communication Status
Business1 New No contact
Business2 New No contact
Business3 New No contact

And then I update the communication status (which will happen for multiple rows), then I want to move it to a different sheet. The columns do not exactly align, so I'd paste each column at a time. There are also two other sheets, for "Contacted" and "Negotiating", so I want to move them into the appropriate sheet based on the Communciation status.

The code I have so far is as follows:


	VB:
	
 
Sub UpdateProspects_2() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowContacted As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "2 - Interested" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"K").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Contacted").Cells(LRowContacted,
"P").Offset(1, 0) 
     
    Sheets("Contacted").Select 
    LRowContacted = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(7, 1), Cells(7, 45)).Copy 
    Range(Cells(7, 1), Cells(LRowContacted, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 
 
 
Sub UpdateProspects_3() 
     
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Application.StatusBar = "Working..." 
     
    Dim SetWbkName As String 
     
    Dim LRowProspects As Long 
    Dim LRowNegotiating As Long 
    Dim HRow As Long 
    On Error Resume Next 
     
    SetWbkName = ThisWorkbook.Name 
     
    Sheets("Prospects").Select 
    LRowProspects = Range("A1048576").End(xlUp).Row 
    HRow = Range("A8").Row 
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Sheets("Prospects").Select 
    Range("A8").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
     
    If Not ActiveSheet.AutoFilterMode Then 
        Selection.AutoFilter 
    End If 
     
     '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     
    ActiveSheet.Range(Cells(8, 1), Cells(LRowProspects, 13)).AutoFilter Field:=4, Criteria1:= _ 
    "3 - Negotiating Deal" 
     
    Range(Cells(HRow + 1, 1), Cells(LRowProspects, 3)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"A").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 4), Cells(LRowProspects, 4)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"E").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 5), Cells(LRowProspects, 8)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"X").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 9), Cells(LRowProspects, 10)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"R").Offset(1, 0) 
     
    Range(Cells(HRow + 1, 11), Cells(LRowProspects, 13)).Copy Destination:=Sheets("Negotiating").Cells(LRowNegotiating,
"AB").Offset(1, 0) 
     
     
    Sheets("Negotiating").Select 
    LRowNegotiating = Range("A1048576").End(xlUp).Row 
     
    Range(Cells(52, 1), Cells(52, 45)).Copy 
    Range(Cells(52, 1), Cells(LRowNegotiating, 45)).PasteSpecial Paste:=xlFormats 
     
     'Delete raw data to save space
    Sheets("Prospects").Select 
     
    With Range(Cells(HRow + 1, 1), Cells(LRowProspects, 13)) 
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete 
    End With 
     
     
    ActiveSheet.ShowAllData 
     
    Application.StatusBar = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My first attempt was to filter the data, copy the visible range, then paste in new tab. However, it works the first time, but usually not the second time. I dont know if its just the volatility associated with this method, in which case, maybe I'd have to sort/copy/paste?

Any help would be greatly appreciated!

I'm using a pivot table to display some calculated values that represent employee utilization for each month in a year. I'm trying to determine how to use VBA to automatically, or on a button click, hide rows where all the values in the year are 0. Attached is as close of a reproduction as I can get. Basically the data is defined in decimals by the week, then I use custom fields in the pivot table to subtract the value from 1. In the month display I average the weeks, as well as display them as percentages.

Right now I've got some conditional formatting set that simply changes the font color to white for any value that is '0'. This is to remove the extraneous numbers in a quick and easy way since we don't care about anyone who has 0 availability for a given month or week. We also don't care about anyone who has 0 availability for the entire year, which is why I would like to completely hide their row if their entire year is 0.

My understanding of filtering within a pivot table is that you can only associate a value filter with one column. Therefore if I tried to use a value filter to not show 0's in the Sum of January column, the filter would remove Ned and Patrick, even though I only want to remove Patrick.

This question is similar to mine, but instead of one cell I want it to apply to the range of cells. Maybe something like IF SUM of January throught April = 0 , then hide the row? The main problem I'm having is translating that code and making it work for me. Let me know if you can help!

I am trying to use VBA to filter a column for more than 2 criteria.

I am using Excel 2003 currently.

I know you can work around the 3 condition max rule in conditional formatting with VBA code, but can you do similar with Autofilter?

e.g.

This works for 2 criteria:

I tried to add another:

and I get Runtime Error: 1004, application-defined or object-defined error.

I also know that I can create a helper column with a formula and filter by that... but wondering if there is a VBA trick out there.

Thanks.

Hi,

I am currently working on a small project to filter dates by column ("date"), I have been using the following snippet of code to achieve what I want :

 'April Alcohol Offense
    Sheets("Alcohol Offense").Select
    ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:= _
        ">=" & Year & "-04-01", Operator:=xlAnd, Criteria2:="<=" & Year &
"-04-30"
   Sheets("calculation").Range("e3") = WorksheetFunction.Subtotal(3, Sheets("Alcohol
Offense").Range("B:B")) - 1
It works well for most of my needs, but it would be nice to select the column by its name rather than by its field number. Is there any way to select the name of the column? Have been encounter some weird problems when using the field, where for some reason the field corresponds to another column and the information is not filtered with the date info.

Any tips on this would be welcomed, many thanks!

Robert

Hello,
I know that this is going to seem really elementary to someone but I could really use some help evaluating the following code. When it comes to VBA I'm a hack, in other words I keep working at it until something works. That being said I've been working on the following code and I keep getting a mistype error when it starts to loop through the cells. Basically what I want the code to do is evaluate the used range in a column for specific values and if those values show up they are to be erased otherwise I want all the other values to be left alone. The only catch is that if the case "R3" shows up on a particular worksheet it is supposed to calculate the amount of days between the date listed in Column "B" and the current date. If it is over six months or about 175 days it clears the contents of the cell. If it is not over six months it leaves the value and goes on to the next cell. Any help is appreciate, here is my code:

	VB:
	
 ClearClassButton() 
    Select Case ActiveSheet.Name 
    Case "Camption Resident Complaint Log" 
        Columns("M:M").Select 
        Selection.ClearContents 
        Range("M1").Select 
        ActiveCell.Value = "CLASS" 
        Range("M4").Select 
        ActiveCell.Value = "CLASS" 
    Case "Timberlane Securitas Log" 
        Set ClassRange = Columns("K") 
        For Each Cell In ClassRange 
            Select Case Cell.Value 
            Case "R3" 
                Date1 = Sheets("Timberlane Securitas Log").Range("B" & Cell.Row).Value 
                Date2 = Date 
                If DateDiff("d", Date1, Date2) > 175 Then 
                    With Cell 
                        .ClearContents 
                    End With 
                End If 
            Case "R1", "R2", "G1", "G2", "G3" 
                With Cell 
                    .ClearContents 
                End With 
            End Select 
        Next Cell 
    Case "Campton Securitas Log", "San Tropico Securitas Log", "Villarrica Securitas Log" 
        Columns("K").Select 
        Selection.ClearContents 
        Range("K1").Select 
        ActiveCell.Value = "CLASS" 
        Range("K1").Select 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank You

I am new to using VBA so please bear with me here. I have a spreadsheet, where I am measuring dimensions for a cpk test. My question is, can I use vba to format the calculated cpk cell so that if it falls below a certain value it replaces that value with whatever I have specified in vba? Was wondering is this is possible. Any help would be appreciated. Thanks guys/gals..

Kevin

I have three columns that I want an automatic coordination for in Ms Excel.?

I am working in Microsoft Excel and I have three columns that I want an automatic coordination for.
I have three columns that I want an automatic coordination for in Ms Excel.?

Column A is for Serial Numbers, Column B is for I.D Number, Column C if for title, Column D for First name, Column E is for Last name. Then Column F is for Amount due, Column G is for Arrears while Column H is for Placement. In Column J, the placement figures have been listed (it will be hidden later), then in column K the Amount due in relation to every particular placement is also listed following the rows of the placement figures. (Column K will also be hidden with the figures later).
My main problem is how to coordinate columns 'H' and 'F', such that, the moment I input the particular placement level listed in column J, the amount attached to that level will automatically display in the relevant cell in Column F to be the amount due.
I have already created a drop down list in column H (Placement) using the placement figures listed in column J. This means that I don't necessarily need to type the placement level in the cells for column H, I could just select from a drop down list upon a click. So the issue is how to automate the other processes so that when a placement level is selected, it puts the amount of money attached to that level in the relevant cell in Column F. I want this to go on throughout the worksheet since I'm dealing with very large number of personnel data.
I need help to do this in Microsoft Excel 2007. Please, I wish to use VBA and Macros to achieve this target but I am very new to the VBA and Macro environment in Excel, so I would like you to generate the code for me and guide me on how to apply it. You can also show me how to generate the VBA codes. I have attached sample copies of the excel document I am working on here to further explain what exactly I intend to do. It is saved in different formats. Thanks [IMG]file:///C:/DOCUME%7E1/BrideyCS/LOCALS%7E1/Temp/moz-screenshot-4.png[/IMG]

Hi

I want to use VBA to do the following.

4 columns sum to equal the 5 column

entering a number into one of the 4 columns retotals the 5 column

changing the 5th column divides the new number by 4 and puts this value
into the each of the first 4 columns.

put another way:

Q1 + Q2 + Q3 +Q4 = Whole Year when columns 1 through 4 are edited

whole year/4 whole year/4 whole year/4 whole year/4 when column whole
year is edited.

I suspect i'll have to do event capturing of cell clicks, move in, move out,
up, down
and find where I am in the spreadsheet for the columns relative to the whole
year column and vice versa

is this feasible or is there another way around this?

thanks

Chris

Hi,

How would I create a VBA macro to check if the same value also exist in the row cell above it (e.g., column P) and then delete the second row with the same value? For example, if the cell in the row above had an "X" and the current row had an "X", delete the second row but not the row with the "X' above. This would check all rows for consecutive "X's" in column P and delete rows with the consecutive "X''s"

Thanks.

Morning all,

I'd like to replace the manual use of the advanced filter for showing unique records with a formula. In the example workbook on the "All WO Numbers" sheet there are 1465 lines containing Work Order numbers. On sheet "Unique WO Numbers" are the 177 unique values found on "All WO Numbers".

The 177 unique value were fetched by applying an advanced filter to 'All WO Numbers'!A:A then the results were copied and pasted to 'Unique WO Numbers'!A:A. Is there a way to use formulas to fill in these unique values automatically.

Thanks all!

With the below code (that was created by Palmetto) in this thread:
http://www.excelforum.com/excel-prog...tion-list.html

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


    Dim bottomrow As Long
    
    bottomrow = Cells(Rows.Count, "B").End(xlUp).Row
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    With Me
        
        If Not Intersect(Target, .Range("D6")) Is Nothing Then
        
            Select Case Target.Value
                Case "All"
                    .AutoFilterMode = False
                    
                Case Is <> "All"
                    .Range("B12:B" & bottomrow).AutoFilter Field:=1, Criteria1:="=" &
Target.Value, visibledropdown:=False
            End Select
        End If
    End With
    
    Application.ScreenUpdating = True

End Sub
I have very limited knowledge with VBA commands. So this is over my head.

I would like to hide Columns E-G or or H-J, depending on what is selected from the list in cell D4.

In conjunction with the above request, I would like to be able to hide columns E-J independently based on what is selected from the list in cell D5.

If this is possible, could you please point me to instructions or demonstrate it for me? Any help is greatly appreciated and I attached the sample Excel file I am working on.

I am trying to figure out how to write a formula that searches column F for multiple values. Those values will either be Regular, Husky, or Slim and they will be in any random order and with duplications.

Basically, I would like for this formula to search column F and if it finds "Regular", then it returns "100", if it doesn't find "Regular, then it searches for "Husky" and returns "200", and if both "Regular" and "Husky" aren't found, it will search for "Slim" and return "300". If none of these values exist, then it returns "000".

I understand the logic of all this, I just can't seem to figure out the proper function to use to make this work.

Anyone know what to do?

I'm trying to use vba to find the last value in a column and then use this value in a simple formula.

All I've been able to figure out is how to "select" this value. I do not know how to "use" this value in my formula.

Using:
Sub last_row_col()
Range("B1000").End(xlUp).Select
End Sub

I've been able to select the value I want. What do I need to do in order to use this value in a formula on my spreadsheet?

Thanks,
James Keeton

Hello,

I am using Excel97 on WINNT.

I am trying to develop an automated method for the formatting of a daily report extracted as a csv file and needing to be cleaned up before sending out to users. In column E of this report is a list of codes representing company names. I have created a VLOOKUP table in a separate sheet and everything works fine with returning the correct names etc.

My problem is that the daily report can range from a few rows of data to several hundred rows and this has the potential to grow into more than a thousand per day.

Provided I enter the VLOOKUP formula manually then fill down the helper column to the last row there's no problem. I would like to automate this feature using VBA but don't know how to write the code so it knows where to stop. I can make a guess that it is unlikely to go past 2000 rows per day but this creates an unnecessary mess.

I have automated all of the other things I need to do with this report. I would like to make this daily exercise as painless as possible and available for anyone in my area to do if I am not at work.

Could someone please assist with the code for this? The VLOOKUP I'm using is as follows:
=VLOOKUP(E2,'C:ZCMP_Listing.xls'!Company_Name,2)

Any help would be gratefully received.

**EDIT** I managed to work this one out. Suffering BIG mental blank after too much time away from any development and too much continuing Christmas cheer. DUUHHH!

Many thanks.

Lee.

I've been putting together an excel spreadsheet that uses dynamically-named lists. It works pretty well, but the magnitude of the spreadsheet is getting to a point where doing this through the cells is becoming a bit problematic. I'm looking for ways that I can use VBA to create the list for me, but I'm having some trouble finding relevant commands.

Consider two worksheets: RawData and ReferenceData

RawData consists of a series of delimeted text, and I am interested in the contents of column B. I cannot guarantee that the series wil be sorted. The data of interest starts in Row 5, and extends for an uncertain number of rows (I could paste different data there with more or fewer rows). However, the last row of applicable data will be blank.

So, out of the whole sheet of data, the "important" parts might look like:
[RawData]B5 => "test1"
[RawData]B6 => "test3"
[RawData]B7 => "test2"
[RawData]B8 => "test4"
[RawData]B9 => blank

ReferenceData consists of a several columns of data against which we will be checking the contents of RawData. For convenience, consider that the column I want to check the RawData against (this time) is column G. Each reference column could vary in length though (column A might only have 5 entries, but column G could have 12). This data is sorted. Again, the last cell in a column of the applicable reference data will be blank.
[ReferenceData]G1 => "test1"
[ReferenceData]G2 => "test2"
[ReferenceData]G3 => blank

I would like the macro to kick in automatically whenever something on RawData changes (i.e., I've pasted new data). When activated, the macro should go down RawData cell by cell, and see if the value lies within a particular ReferenceData column. If so, the RawData cell value gets added to List. If not, the RawData cell value skips to the next cell.

So, after the macro runs, I should have a list, that I can refer to elsewhere (say, in data validation, or used with other lists merged into a single new one). List, then, would be "test1, test2"

And so I present a teaching opportunity I know how to describe what I want done, but need some guidance on the syntax.

Thanks!

I have a named range $B$11:$M$14 of which I used the below reference to create. The cells in this range are randomly coloured and the user drags them out to re group in colour groups in another part of the worksheet.
I need this named range to remain un changed in its position but I need to allow the user to freely remove groups of cells (by dragging) from the range area without upsetting/changing the originally set range.
To achieve this I used INDEX to anchor the range in its relevant position to remain constant. (otherwise dragging large groups of cells out of the range causes the named range to adjust.)

My problem now is -
That I need to be able to add and remove columns within the named range using a VBA sub() in a different workbook (Workbook2) as necessary, and also adjust the named range accordingly as I add the columns.

The only constant is the top left cell of the range $B$11 if this helps? But the width of the range may vary depending on the Workbook being used by Workbook2 at the time.
How do I do this?

Starting named range is below.

	VB:
	
=INDEX(Sheet1!$A:$Z, 11,2):INDEX(Sheet1!$A:$Z, 14,13) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to use VBA to create an entire userForm from code. For a previous spreadsheet I had already created the userform that I wanted, but now I'm trying to make the spreadsheet more applicable to scenarios outside of my original.

The following code creates the userform, adds the multipage with # of tabs and names created by ranges in the spreadsheet. But I can't figure out how to get the code to add textboxes (checkboxes, comboboxes, etc.) to each page of the multipage.


	VB:
	
 MakeForm() 
     
    Dim TempForm As Object ' VBComponent
    Dim FormName As String 
    Dim NewButton As MSForms.CommandButton, NewMulti As MSForms.MultiPage 
    Dim NewPage As MSForms.Pages 
    Dim NewTbx As MSForms.TextBox, NewCbo As MSForms.ComboBox 
    Dim NewChk As MSForms.CheckBox, NewLbl As MSForms.Label 
    Dim TextLocation As Integer 
     
    Dim loopvar1 As Integer, loopvar2 As Integer, loopvar3 As Integer 
     
    Application.VBE.MainWindow.Visible = False 
    Application.ScreenUpdating = False 
     
     '   Create the UserForm
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3) 
     
    With TempForm 
        .Properties("Caption") = TempForm.Name 
        .Properties("Width") = 350 
        .Properties("Height") = 350 
    End With 
    FormName = TempForm.Name 
     
     '   Add a MultiPage
    Set NewMulti = TempForm.designer.Controls.Add("forms.MultiPage.1") 
    With NewMulti 
        .Pages.Remove (1) 'must delete the two pages that are automatically created
        .Pages.Remove (0) 
        .Name = "MultiPage1" 
        .Width = 340 
        .Height = 250 
        .Left = 0 
        .Top = 5 
        .Pages.Add ("Page 1") 
        For loopvar1 = 1 To Range("numTabs").Value 
            .Pages.Add (Range("tab" & loopvar1 & "name").Value) 
        Next 
    End With 
     
     ' loop to add features to the plan design tabs of multipage
    For loopvar1 = 1 To Range("numTabs").Value 
        Set NewPage = TempForm.designer.Controls.forms.MultiPage.Pages(loopvar1) 
        For loopvar2 = 1 To 4 'TextBoxes
            With NewPage 
                 
                 'this is where I want to add the items to go on the multipage
                 
            End With 
        Next 
    Next 
     
    VBA.UserForms.Add(FormName).Show 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Tabs other than "Page 1" will be the same in design.

I'm expecting that the issue is in the Set NewPage command, but then I also can't find the right command to create the items wanted.

Here's what the existing form created in VBE looks like and what I'm trying to replicate through VBA.
userform.JPG

Thanks for any and all help.

I have the following list (below) of data that I usually export from a database into Excel . In excel the items are categorised into sub-totals using the location or cost centre code. The item code (F00904, F00905 etc) appear in the same column as the sub-total codes or location codes (25AO-GHRRR, 25AT-GHRRR etc ). I need to insert two column and, for each item code in a row, enter its location code as well as the location name. I have done an example below . I have about 1200 rows of data. Is there any simpler way using VBA to handle this issue.

Code Description Amount
F00904 VAS.100G 440,000.00
F00905 VAS.250G 440,000.00
F00906 VAS. 25G 350,000.00
F00907 VAS.300G 350,000.00
F01740 VAS.700G 350,000.00
25AO-GHRRR OMO 1,930,000.00
F01746 CLARICER -500 350,000.00
F01747 CLARICER - 300 315,000.00
F01748 CLARICER 425GM MOULD 13,000.00
25AT-GHRRR KATA 678,000.00
F01749 OIL PHASE PUMP 49,250.00
F01750 REWORK PUMP 360,000.00
F01751 ROLLER CONVEYOR 171.00
F01752 STAINLESS STEEL TANK 28,160.00
F02032 REJECT OIL TANK 714.00
25DT-GHRRR MAK 438,295.00
F02977 AQUEOS TANK IN S/S 102,000.00
F01783 HOT WATER TANK 28,160.00
25DU-GHRRR KOV 130,160.00
F00604 OIL PHASE TANK 56,881.00
25KE-GHRRR TAM 56,881.00
F00605 DRAWER FILING CABINET 1,078.00
F00606 SNAP CABINET WITH DOORS 28,160.00
F00607 LEVEL 1 OFFICE DESK 51,259.00
F00608 TIME & ACCESS CONTROL 28,160.00
F00609 PROCESS CHECKING EQUIP 28,160.00
F00610 PIPING 1,163.00
F00611 TGV CUTTER 24.00
F00612 CALORIFIER PUM 118.00
25HA-GHRRR BAM 138,122.00

Report total 3,371,458.00

Work to be done: Example

Item Code Loc Code Loc. Name Description Amount
F00904 25AO-GHRRR OMO VAS.100G 440,000.00
F00905 25AO-GHRRR OMO VAS.250G 440,000.00
F00906 25AO-GHRRR OMO VAS. 25G 350,000.00
F00907 25AO-GHRRR OMO VAS.300G 350,000.00
F01740 25AO-GHRRR OMO VAS.700G 350,000.00
F01746 25AT-GHFCR KATA CLARICER-500 350,000.00
F01747 25AT-GHFCR KATA CLARICER-300 315,000.00
F01748 25AT-GHFCR KATA CLARICER 425GM 13,000.00

Hi,

I have three columns of data, and I need to find the maximum value of the third column when the first two meet specific criteria.

Column H contains a percentage.
Column I contains a priority (i.e high, medium, low)

I need to find tha max value of column V for specific values i.e H =

I know I have seen a similar question, but I did a search for "Sorting
Multiple Criteria" and did not find what I am looking for.

Is there a way to use VBA to sort by more than three criteria?

Specifically four criteria...

Example: The Header row is row 2

Selection.Sort _
Key1:=Range("E2"), Order1:=xlAscending, _
Key2:=Range("H2"), Order2:=xlAscending, _
Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

What the operators would like is

Selection.Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("E2"), Order2:=xlAscending, _
Key3:=Range("H2"), Order3:=xlAscending, _
Key4:=Range("B2"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

but this creates errors

--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=551157

Hello,

I'm trying to use VBA to save a copy of a workbook, I want to just save the
values not all the formulas and links. So I've got the macro to copy the
sheet and then paste special with values only and then I want to Save it. I
want to be prompted where to save it. I've tried using activeworkbook.close
but because I have a workbook before close event macro it just closes without
saving the changes.
Is there anyway I can either stop the before close macro running or can I
get SaveCopyAs to ask for a filename


No luck finding an answer? You could always try Google.