Email:      Pass:    Pass?
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Macro Or Excel If Statement

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I need a little help on whether this should be done with a Macro or an IF statement.

On the first tab, there is a field that the user inputs, and then i need to go to the second tab and if the field is in between the range of the column C and D, then i need to return Column A and E back to the first tab. But if it is not in the range, it needs to go down to the next line, and the next line until the range is met. Any ideas? I need a little help.

THANKS!

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from
Sort Data that Doesn't Have Headers in Ascending Order in Excel
- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum
Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
- This Excel macro filters data in Excel without the filter "arrow" appearing in the column that is being filtered. This

Similar Topics







I need a little help on whether this should be done with a Macro or an IF statement.

On the first tab, there is a field that the user inputs, and then i need to go to the second tab and if the field is in between the range of the column C and D, then i need to return Column A and E back to the first tab. But if it is not in the range, it needs to go down to the next line, and the next line until the range is met. Any ideas? I need a little help.

THANKS!!!


I need a little help on whether this should be done with a Macro or an IF statement.

On the first tab, there is a field that the user inputs, and then i need to go to the second tab and if the field is in between the range of the column C and D, then i need to return Column A and E back to the first tab. But if it is not in the range, it needs to go down to the next line, and the next line until the range is met. Any ideas? I need a little help.

Thanks


I run a macro routine that updates a pivot table page by field by looping through a list of data. When the page by field is updated the macro calls on a second macro to run an email routine in which it attaches a file to an email.

There is a line in the second macro that sets the range of cell to copy and paste into the new workbook that is attached listed below.

Macro Mail_Range
Set Source = Range("A1:J66")

I run this macro on 5 different sheets and each sheet would has a different copy range to get all of the data in.

I cannot put a prompt into the second macro to ask for a last line of data to set the range because when it loops it will ask time for the range.


Is there a way in the first macro to insert some code that will update this line of code in the second macro?

I am thinking either a message box that appears and asks what the last row of the print range is could be inserted before the macro runs its loop and the entered value would go into the second macro and update the field above, or I could have a cell in each page in that ithe last row number is entered in to update the field.

Hope this isn't too confusing


Here is my macro. Please tell me what I am doing wrong.

I have two columns of data, and a third column for which a value will be input as a reuslt of this macro. I seem to be having a problem with "Garage Attached" in AI2:AI10. The field in this column shows this value: Driveway Concrete Garage Attached 2 car. I want the macro to return the cell value on if there is the exact wording of "Garage Attached" in the AI2 field. This macro gives me that value, but also give the same value if nothing in present in the AI2 field. What am I doing wrong?



Range("v2:v10").Select
For Each CELL In Selection
If Range("AI2").Value = "Garage Attached" And _
Range("W2").Value = "2" Then _
CELL.Value = "2"
Next CELL


I have a large text file over 300 pages where every four lines are one record. And then a blank line is in between where one record.
One problem is the first four lines of the record columns 1-13 is the first field.
columns 14-28 is the second field .
columns 28-39 is the third field.
columns 40-68 is the fourth field.
columns 69-73 is the fifth field.
columns 74-85 is the sixth field.
columns 86-90 is the seventh field.
columns 91-95 is the eight field.
columns 96-132 is the nineth field.
Next columns 1-13 on the second line is the 10th field.
columns 14-28 on the second line is the 11th field.
columns 28-39 on the second line is the 12th field.
columns 40-68 on the second line is the 13th field.
columns 69-73 on the second line is the 14th field.
columns 74-85 on the second line is the 15th field.
columns 86-90 on the second line is the 16th field.
columns 91-95 on the second line is the 17th field.
columns 96-132 on the second line is the 18th field.
Next columns 1-13 on the third line is the 19th field.
columns 14-28 on the third line is the 20th field.
columns 28-39 on the third line is the 21st field.
columns 40-68 on the third line is the 22nd field.
columns 69-73 on the third line is the 23rd field.
columns 74-85 on the third line is the 24th field.
columns 86-90 on the third line is the 25th field.
columns 91-95 on the third line is the 26th field.
columns 96-132 on the third line is the 27th field.
Next columns 1-95 on the fourth line is the 28th field.
columns 96-132 on the fourth line is the 29th field.

Then a blank line is in between every four lines

How can I import every four lines into access using the field layouts above ?

Yaneckc


I'm trying to create a search input row at the top of my worksheet which will autofilter the columns below based on the inputs. It's basically like autofilter, but just more convenient for my purpose.

The code below lets me filter based on the exact input I type into the search row cells. However, I need help to convert this thing into a CONTAINS search rather than an EQUALS search.

Can anyone recommend the necessary modifications to the code. The goal would be to have it filter even partial matches (e.g., if I enter FAS 123, I want the filtered list to show both FAS 123 and FAS 123R).


Other details:
My spreadsheet has a database array A6:K300, with Row 6 being the column header row. In row 5, above each column header, I can input a search term, and have the column directly below it filter based on what I entered in row 5. Currently, it finds exact matches, but I'm hoping to make it more useful by having it return partial results using a CONTAINS type autofilter.

Thanks!



Here's the code:
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5:K5")) Is Nothing Then
        Select Case Target.Address
            Case Is = "$A$5"
                If Range("A5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=1
                Else
                    Range("A6:B300").AutoFilter Field:=1, _
                        Criteria1:=Range("A5").Value
                End If
            Case Is = "$B$5"
                If Range("B5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=2
                Else
                    Range("A6:B300").AutoFilter Field:=2, _
                        Criteria1:=Range("B5").Value
                End If
            Case Is = "$C$5"
                If Range("C5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=3
                Else
                    Range("A6:B300").AutoFilter Field:=3, _
                        Criteria1:=Range("C5").Value
                End If
            Case Is = "$D$5"
                If Range("D5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=4
                Else
                    Range("A6:B300").AutoFilter Field:=4, _
                        Criteria1:=Range("D5").Value
                End If
            Case Is = "$E$5"
                If Range("E5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=5
                Else
                    Range("A6:B300").AutoFilter Field:=5, _
                        Criteria1:=Range("E5").Value
                End If
            Case Is = "$F$5"
                If Range("F5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=6
                Else
                    Range("A6:B300").AutoFilter Field:=6, _
                        Criteria1:=Range("F5").Value
                End If
            Case Is = "$G$5"
                If Range("G5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=7
                Else
                    Range("A6:B300").AutoFilter Field:=7, _
                        Criteria1:=Range("G5").Value
                End If
            Case Is = "$H$5"
                If Range("H5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=8
                Else
                    Range("A6:B300").AutoFilter Field:=8, _
                        Criteria1:=Range("H5").Value
                End If
            Case Is = "$I$5"
                If Range("I5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=9
                Else
                    Range("A6:B300").AutoFilter Field:=9, _
                        Criteria1:=Range("I5").Value
                End If
            Case Is = "$J$5"
                If Range("J5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=10
                Else
                    Range("A6:B300").AutoFilter Field:=10, _
                        Criteria1:=Range("J5").Value
                End If
            Case Is = "$K$5"
                If Range("K5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=10
                Else
                    Range("A6:B300").AutoFilter Field:=10, _
                        Criteria1:=Range("K5").Value
                End If
        End Select
    End If
End Sub





Hi guys

I am trying to perform some filters on a table but only if the criteria for that field is not blank. So for the first line below, only perform it if the cell B1 is not blank. Im thinking maybe a series of ElseIF statements or a loop/next function.

I've been experimenting and its proving a bit elusive for me. Would really appreciate some assistance.

Cheers
Al


Selection.AutoFilter Field:=1, Criteria1:=ws1.Range("B1").Value
Selection.AutoFilter Field:=2, Criteria1:=ws1.Range("D1").Value
Selection.AutoFilter Field:=3, Criteria1:=ws1.Range("F1").Value
Selection.AutoFilter Field:=4, Criteria1:=ws1.Range("H1").Value
Selection.AutoFilter Field:=5, Criteria1:=ws1.Range("J1").Value


I am trying to create an Expense Statement worksheet for my users. When the user opens a Statement for the first time, the date field at the top is blank. The first column is where they enter a date. I want the spreadsheet to use the date from the first entry (wherever that entry may be in the date column) and display it in the date field at the top. If the date field at the top has already been filled, then it is not to alter the contents of the field. It is supposed to leave the cell alone and do nothing.

Does anyone have any suggestions for how I can construct my IF statement to handle this problem. In effect, I want to create an IF statement which says

If count(A1:A20)=1,
then update the cell
else leave the cell alone

Thank you for your ideas


Brian


I'm having a problem with this macro.

Why there's a problem is that I'm using autofill; however, some times the range selected to be autofilled is only 1 line. How do I create an IF statement that looks at the selected range:

If Range is only 1 line then End IF

Else

etc.

Here's my code:

THE PROBLEM IS this line:
The problem is, on this file, the selection of K4 and Description highlights only one line.

Selection.AutoFill Destination:=Range("K4", Description), Type:=xlFillDefault

FYI:
Dim Description As Range
Set Description = Range("J4:J65536").Find(What:="", LookAt:=xlPart).Offset(-2, 1)


I am trying to place a if statement in a field using a macro. If the response if 0, return a cell conent, otherwise return a blank cell. The way I am using the quotes is causing problems with the macro. How else can I tell Excel to return a blank cell without using "". I have attached the command line below.

Range("W7").Formula = "=if(Calculations!BB70, Calculations!BB7,"")"

Thanks


This is a new question based on another thread that contains a solution that I want to tweak.

That thread implements a row that filters the rows below it based on its inputs. Basically, an 'equals' autofilter, but you can type in the criteria in the cells above the column headers instead of going into the autofilter itself.


Here's what I need:
As a variant on that thread, I would like to modify the code to filter on anything that CONTAINS the inputs (not equals).

For example, my Array is A6:K300, with row 6 being column headers, I want Row 5 to essentially be a filter terms row, where I can enter partial phrases in row 5 and have the columns directly beneath them autofilter if the phrase is contained in any of the array rows. If I enter '123' in a cell in row 5, I want the filter results to show '123' and '123R', etc.


Here is my current code:


Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5:K5")) Is Nothing Then
        Select Case Target.Address
            Case Is = "$A$5"
                If Range("A5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=1
                Else
                    Range("A6:B300").AutoFilter Field:=1, _
                        Criteria1:=Range("A5").Value
                End If
            Case Is = "$B$5"
                If Range("B5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=2
                Else
                    Range("A6:B300").AutoFilter Field:=2, _
                        Criteria1:=Range("B5").Value
                End If
            Case Is = "$C$5"
                If Range("C5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=3
                Else
                    Range("A6:B300").AutoFilter Field:=3, _
                        Criteria1:=Range("C5").Value
                End If
            Case Is = "$D$5"
                If Range("D5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=4
                Else
                    Range("A6:B300").AutoFilter Field:=4, _
                        Criteria1:=Range("D5").Value
                End If
            Case Is = "$E$5"
                If Range("E5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=5
                Else
                    Range("A6:B300").AutoFilter Field:=5, _
                        Criteria1:=Range("E5").Value
                End If
            Case Is = "$F$5"
                If Range("F5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=6
                Else
                    Range("A6:B300").AutoFilter Field:=6, _
                        Criteria1:=Range("F5").Value
                End If
            Case Is = "$G$5"
                If Range("G5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=7
                Else
                    Range("A6:B300").AutoFilter Field:=7, _
                        Criteria1:=Range("G5").Value
                End If
            Case Is = "$H$5"
                If Range("H5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=8
                Else
                    Range("A6:B300").AutoFilter Field:=8, _
                        Criteria1:=Range("H5").Value
                End If
            Case Is = "$I$5"
                If Range("I5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=9
                Else
                    Range("A6:B300").AutoFilter Field:=9, _
                        Criteria1:=Range("I5").Value
                End If
            Case Is = "$J$5"
                If Range("J5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=10
                Else
                    Range("A6:B300").AutoFilter Field:=10, _
                        Criteria1:=Range("J5").Value
                End If
            Case Is = "$K$5"
                If Range("K5").Value = "" Then
                    Range("A6:B300").AutoFilter Field:=10
                Else
                    Range("A6:B300").AutoFilter Field:=10, _
                        Criteria1:=Range("K5").Value
                End If
        End Select
    End If
End Sub



Thanks for the help!

Jeff


To solve the problem of not being able to use an IF statement in a calculated field I used the pivot table options to make errors return N/A instead of #DIV. Now my grand total line for this field which is a percentage returns the same N/A.

I have fields A, B, and C.

Calculated field is C/B in which B is sometimes 0.

In essence the grand total line for the calculated item should still equal grand total of column C/B but it returns the N/A as stated.

Any suggestions?


I've searched for a similar thread reguarding my issue with no luck.
I need to autofilter a field using a macro, however I keep getting this error: Expected: End of statement.

Here is the code:

Worksheets("Sheet1").Select
Rows("2:2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=Worksheets("Sheet2").Range("E3").Value
Selection.AutoFilter Field:=4, Criteria1:= ">=Worksheets(" Sheet2 ").Range("E5").Value", Operator:=xlAnd

Basically all I want the macro to do is filter any number that is greater than the value found in cell E5 of SHEET2. I understand that it errors out because it sees the end quote at SHEET2 . It needs to ignore the quotes around SHEETS2. The macro works for the line above the one in red because it uses "=" instead of ">=". Please help. Thanks.




I have the below code being used. The problem is it only searches for exact words. So if I type in the name white to D11 and run the macro I only get the name white. I am looking for it to serach using contain white and return all results with that string of characters. The only way I have been able to do this is to type * before and after my word in the actual cell. Can anyone tell me how to make my macro do this for me?

Sub searchforlastnameaccessroster()
'
' searchforlastname Macro
'
ActiveSheet.Unprotect Password:="pass"
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=1
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=2
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=3
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=4
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=5
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=6
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=7
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=8
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=9
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=10
Range("D11").Select
ActiveSheet.Range("$B$30:$N$417").AutoFilter Field:=1, Criteria1:="=" & Range("D11").Value _
, Operator:=xlAnd
ActiveWindow.SmallScroll Down:=-3
Range("C13").Select
ActiveSheet.Protect Password:="pass"
End Sub

I am trying to get my list to sort based on the combined values of three check boxes. I created a line for each possible combination and for some reason it doesn't work properly. I think it has to do with my coding to get it to only perform an action if all three values are met. Here is the code, let me know if you see a problem:

If Range("AH2") = True And Range("AH3") = True And Range("AH4") = True Then
Selection.AutoFilter Field:=25 'Note : All
ElseIf Range("AH2") = False And Range("AH3") = True And Range("AH4") = True Then
Selection.AutoFilter Field:=25, Criteria1:="High" 'Note : Medium and Low
ElseIf Range("AH2") = False And Range("AH3") = False And Range("AH4") = True Then
Selection.AutoFilter Field:=25, Criteria1:="High", Operator:=xlAnd, _
Criteria2:="Medium" 'Note : Low only
ElseIf Range("AH2") = False And Range("AH3") = False And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="=" 'Note: None Selected
ElseIf Range("AH2") = True And Range("AH3") = False And Range("AH4") = True Then
Selection.AutoFilter Field:=25, Criteria1:="Medium" 'Note : High and Low
ElseIf Range("AH2") = True And Range("AH3") = False And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="Medium", Operator:=xlAnd, _
Criteria2:="Low" 'Note : High only
ElseIf Range("AH2") = Trues And Range("AH3") = True And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="Low" 'Note : High and Medium
ElseIf Range("AH2") = False And Range("AH3") = True And Range("AH4") = False Then
Selection.AutoFilter Field:=25, Criteria1:="High", Operator:=xlAnd, _
Criteria2:="Low" 'Note : Medium only


I have created a code for transposing the data from cols. to rows. I have first counted the now of col. to be converted and then added as many rows. I have used case statement to fill the values in the rows.

There are 49 cases in the statement and i am getting an Compile error 'Procedure too long'

Have pasted the code of last case statement

Code:

Case 49
Me.Range("AC" & k + 1).Value = Me.Range("AD" & k).Value
Me.Range("AC" & k + 2).Value = Me.Range("AE" & k).Value
Me.Range("AC" & k + 3).Value = Me.Range("AF" & k).Value
Me.Range("AC" & k + 4).Value = Me.Range("AG" & k).Value
Me.Range("AC" & k + 5).Value = Me.Range("AH" & k).Value
Me.Range("AC" & k + 6).Value = Me.Range("AI" & k).Value
Me.Range("AC" & k + 7).Value = Me.Range("AJ" & k).Value
Me.Range("AC" & k + 8).Value = Me.Range("AK" & k).Value
Me.Range("AC" & k + 9).Value = Me.Range("AL" & k).Value
Me.Range("AC" & k + 10).Value = Me.Range("AM" & k).Value
Me.Range("AC" & k + 11).Value = Me.Range("AN" & k).Value
Me.Range("AC" & k + 12).Value = Me.Range("AO" & k).Value
Me.Range("AC" & k + 13).Value = Me.Range("AP" & k).Value
Me.Range("AC" & k + 14).Value = Me.Range("AQ" & k).Value
Me.Range("AC" & k + 15).Value = Me.Range("AR" & k).Value
Me.Range("AC" & k + 16).Value = Me.Range("AS" & k).Value
Me.Range("AC" & k + 17).Value = Me.Range("AT" & k).Value
Me.Range("AC" & k + 18).Value = Me.Range("AU" & k).Value
Me.Range("AC" & k + 19).Value = Me.Range("AV" & k).Value
Me.Range("AC" & k + 20).Value = Me.Range("AW" & k).Value
Me.Range("AC" & k + 21).Value = Me.Range("AX" & k).Value
Me.Range("AC" & k + 22).Value = Me.Range("AY" & k).Value
Me.Range("AC" & k + 23).Value = Me.Range("AZ" & k).Value
Me.Range("AC" & k + 24).Value = Me.Range("BA" & k).Value
Me.Range("AC" & k + 25).Value = Me.Range("BB" & k).Value
Me.Range("AC" & k + 26).Value = Me.Range("BC" & k).Value
Me.Range("AC" & k + 27).Value = Me.Range("BD" & k).Value
Me.Range("AC" & k + 28).Value = Me.Range("BE" & k).Value
Me.Range("AC" & k + 29).Value = Me.Range("BF" & k).Value
Me.Range("AC" & k + 30).Value = Me.Range("BG" & k).Value
Me.Range("AC" & k + 31).Value = Me.Range("BH" & k).Value
Me.Range("AC" & k + 32).Value = Me.Range("BI" & k).Value
Me.Range("AC" & k + 33).Value = Me.Range("BJ" & k).Value
Me.Range("AC" & k + 34).Value = Me.Range("BK" & k).Value
Me.Range("AC" & k + 35).Value = Me.Range("BL" & k).Value
Me.Range("AC" & k + 36).Value = Me.Range("BM" & k).Value
Me.Range("AC" & k + 37).Value = Me.Range("BN" & k).Value
Me.Range("AC" & k + 38).Value = Me.Range("BO" & k).Value
Me.Range("AC" & k + 39).Value = Me.Range("BP" & k).Value
Me.Range("AC" & k + 40).Value = Me.Range("BQ" & k).Value
Me.Range("AC" & k + 41).Value = Me.Range("BR" & k).Value
Me.Range("AC" & k + 42).Value = Me.Range("BS" & k).Value
Me.Range("AC" & k + 43).Value = Me.Range("BT" & k).Value
Me.Range("AC" & k + 44).Value = Me.Range("BU" & k).Value
Me.Range("AC" & k + 45).Value = Me.Range("BV" & k).Value
Me.Range("AC" & k + 46).Value = Me.Range("BW" & k).Value
Me.Range("AC" & k + 47).Value = Me.Range("BX" & k).Value
Me.Range("AC" & k + 48).Value = Me.Range("BY" & k).Value





Hi Folks
I need your expertise on the second half of this coding.

Basically, after I've copied and pasted the formula, I'm setting the autofilter to 'Current Member'.

What I'm trying to get the macro to do is, to go to the last line of data, select the row and go to the top line and delete (as I only want to view 'Terminated').

What its currently doing, is that it is going to the last line in the spreadsheet (65536) and then deleting from there. However, when I check it to a manual run, it seems to delete one extra row???

Maybe an 'if' statement can be used??

Appreciate any help on this.

Cheers

Code:

Sub Terminated()
    Range("G2").Select
    Selection.Copy
    Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Selection.AutoFilter Field:=7, Criteria1:="Current Member"
    Selection.End(xlDown).Select
    Rows.Select
    Range(Selection, Cells(1)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=7
End Sub





Hey Guys,

Nice and simple macro...
Code:

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("L2:L" & lastrow).Formula = "=CEILING(RC[-1],30)/60"
    Selection.AutoFilter Field:=3, Criteria1:="=MO", Operator:=xlOr, _
        Criteria2:="=MOV"
    Range("J2:J" & lastrow).Formula = "=(MAX(2,RC[2]/0.5))*R1C13/2"
    Selection.AutoFilter Field:=3, Criteria1:="RO"
    Range("J2:J" & lastrow).Formula = "=MAX(1,RC[2])"
    Selection.AutoFilter Field:=3, Criteria1:="TXO"
    Range("J2:J" & lastrow).Formula = "0.75"
    Selection.AutoFilter Field:=3
    Selection.AutoFilter Field:=4, Criteria1:="=*KB roam*"
    Range("J2:J" & lastrow).Formula = "=RC[-1]"
    Selection.AutoFilter Field:=4
    Selection.AutoFilter Field:=10, Criteria1:=""
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-4])"


But everytime it selects a new filter criteria, it inputs the Column S formula throughout the whole worksheet of column S, not just the specific rows that i want (pre-selected with the autofilter).
Is there a way to make the formula in Column S only occur to the cells that i want with the autofiler?

Cheers all


Hi there

I have a file that we output each day where I need to split the detail of a column based on some variables.

In the sample, you can see the different types of outputs that are available. For each output, there can be up to 3 "Lines:" depending on user input. The only mandatory field is "Field One:". The 3 "Lines" can have a maximum of 18 characters only (but anything under that is ok), if that is of any use. The examples shown are the format the database outputs depending on how many lines of info the user inputs (what I mean is the only variables are the bits between the "Line" and the
.

What I want to be able to do is split the data in the cell to extract the info from Field One, Line 1, Line 2 and Line 3 if they exist. Ideally what I'd like to do is insert 4 columns to the right of the existing data, put in a "header row" of Field One, Line 1, Line 2, Line 3 and then have the data populate under those headings, if that is possible.

Unfortunately I cannot change the output from the database, so that is not an option.

Have I got any hope of achieving this?

Cheers
Griffo

Field One:NIPb r
Field One:KODAb rLine 1:12334567890b r
Field One:Tacob rLine 1:123 Test Streetb rLine 2:123 456 7890b r
Field One:TEDb rLine 1:Ph: 1234 567 890b rLine 2:18 Testing Streetb rLine 3:City Details Hereb r

EDIT - changed to b r as didn't realise it was going to insert breaks! b r exports as br with around the br


Hi

I need help with the below macro, I'm trying to input the word "blank" in row 10 of column A thru F, if the cell is empty. However, I don't think my Dim statement is correct, any help would be appreciated.

Quote:

Sub testRow()

Dim I As Integer


For I = A To F
Range("10" & I).Select
If Range("10" & I) = "" Then
Range("10" & I) = "Blank"
End If


Next I

End Sub




The text file that i am trying to import looks like this:

Field 1 <tab> Field 2 <tab> Field 3.1(CR)Field 3.2(CR)Field 3.3<tab>Field 4

where <tab> is Tab, and (CR) is carriage return. When importing into Excel, i want all 3 lines in Field 3 (Field 3.1, Field 3.2 and Field 3.3) be in one cell, but multiline. So basically i want excel to ignore carriage return. How do i do that???
I have some control on which characters to use to delimit the text file (e.g. i can change <tab> and (CR) to whatever i want. here is what i have tried so far:

changed (CR) character to carriage return (Ascii 13)
changed (CR) character to Line Feed (Ascii 10)
changed (CR) character to carriage return + Line Feed (Ascii 13 + Ascii 10)
Tried enclosing Field 3.1(CR)Field 3.2(CR)Field 3.3 into single or double quotes.

none of these seem to work: I get this in output:
Field 1 Field 2 Field 3.1
Field 3.2
Field 3.3 Field 4

does anyone know of a solution. it would be greatly appreciated


Below is my code. Very simply what I am trying to accomplish is as follows. The end user is on the sheet called "Home" in the workbook. The user selects a button that pops up a userform for them to make 2 simple selections, then when they click OK. My code goes to a sheet called "Log" and uses the autofilter mode to return what they need.

Private Sub CommandButton1_Click()


'Place Data on Sheet1

Sheets("Sheet1").Range("IR1").Value = Summary.ComboBox1.Value
Sheets("Sheet1").Range("IS1").Value = Summary.ComboBox2.Value
Unload Me

Application.ScreenUpdating = False

'Filter Log

Sheets("Log").Select
Range("A1").Select
Selection.AutoFilter

If Sheets("Sheet1").Range("IR1").Value "" Then
Selection.AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Range("IR1").Value
Else
Selection.AutoFilter Field:=1
End If

If Sheets("Sheet1").Range("IS1").Value "" Then
Selection.AutoFilter Field:=2, Criteria1:=Sheets("Sheet1").Range("IS1").Value
Else
Selection.AutoFilter Field:=2
End If

Sheets("Log").Select


End Sub


Everything works perfect except the last line in my code. It will not go to the sheet "Log" for the user to view. The screen remains on the Sheet "Home"??? I have tried taking adding a line turning the screen updating back on and I have just removed the screen updating code completely but still no luck.



Any help with this is appreciated....I know it something simple I am missing!

Thanks!


Hi,

I have a text field at the bottom of a user form that remains hidden (i.e. visible = false) until the user clicks the "Ok" button. At that point, I want the text field to appear as the macro is running (it's a large macro, so the text field just says "processing, please wait...").

The first line of my macro is:

Code:

CostSummSettings.Processing.Visible = True


Where CostSummSettings.Processing is the text field. The rest of the code comes after that line. The user form doesn't close until the end of the macro, so the form should stay open with this text field displayed on it as the macro is running. However, when the user clicks OK and the macro is run, the text field is not appearing on the form. I've isolated and tested that line of code and it works on its own, but not when it's combined with the rest of the code.

Can't figure out why. Any suggestions? Thanks in advance.


I am having trouble with an Append table in Access 2003 giving me the following message:

The Insert INTO statement contains the following unknow field name:'field name xyz'. make sure you have typed the name correctly, and try the operation again.

I have used the drop down box on the "Append To" drop down box and changed the format in the SQL. For some reason it reverts back to a spelling that is not on the original table.

The field name is "Address1 (ShipTo)" and it changes it to "Address1(ShipTo)" automatically - essentialy no space between the "1" and the "(". I don't know why it changes the field name, but it is not found anywhere.

Any ideas?


I am having issues with this Macro since I have to run it on computers only supporting Excel 2003 and computers with Excel 2007. The Macro in use is as follows:
--------------------------------------------------------------------------------------------------------------------------------
Code:

Sub FilterMacro()
Sheets(1).ListObjects("Table1").Range.AutoFilter Field:=1
Sheets(1).ListObjects("Table1").Range.AutoFilter Field:=4
'Note: No content can be in sheet 2 except in row 1 or else the following 'line will delete it after macro activates
'Note: Table1 filter elements will be used during macro so any current filters 'will be discarded
Sheets(2).UsedRange.Offset(1).Clear
'The following searches and filters by agent and date (between)
Sheets(1).ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        Range("Sheet2!A1").Value
    Sheets(1).ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:= _
        ">=" & Range("Sheet2!B1").Value, Operator:=xlAnd, Criteria2:="<=" & Range("Sheet2!C1").Value
Sheets(1).Range("Table1").Copy Destination:=Sheet2.Range("A5")
'These following lines revert filters in table1
Sheets(1).ListObjects("Table1").Range.AutoFilter Field:=1
Sheets(1).ListObjects("Table1").Range.AutoFilter Field:=4
End Sub


--------------------------------------------------------------------------------------------------------------------------------
What i need the macro to do is to instead of trying to run from a table (since 2003 does not support it) is to have it find an agent name in Column A of sheet one if it is within the date range selected (date is located in Column D of sheet one) and copy and paste the matching criteria to sheet 2 from A:I. Any suggestions or help on this would be great!

Thank you in advance!!!

Eric