For Each Cell In Range("L1:AX1") Select Case True Case Cell Like "*XBA*" Cell.EntireColumn.Delete Case Cell Like "LBO*" Cell.EntireColumn.Delete End Select Next CellIs there a way two do this without splitting it up into two statements?
I would use a SUMPRODUCT if using non-VBA method, equivalent would be:
I have tried using Union and Evaluate to join the criterion ranges PcCol and AcCol but to no avail. This is embedded in a loop which could have up to 50000 iterations so I think I would like to avoid using .Formula="=SUMPRODUCT(etc)" method.
Does anybody have a good idea about how to achieve equivalent multiple criterion SumIf in VBA?
For reference, PcCol, AcCol and pVal and column ranges returned thru Inputbox Type:=8.
With WorksheetFunction SubstString = Trim(.Substitute(ActiveSheet.Cells(i, 2).Value, "EST OF", "")) SubstString = Trim(.Substitute(SubstString, "(TRUST)", "")) SubstString = Trim(.Substitute(SubstString, "(IND)", "")) Range("C" & i).Value = SubstString End WithAnd the list of things I need to substitute can grow. Is there a better way to do multiple substitutions like this in VBA?
VB:Thank youChartTitles() Dim sht As Worksheet Dim ChtTitle As String For Each sht In ThisWorkbook.Worksheets If TypeName(ActiveSheet) = "Chart" Then Select Case ActiveChart.Name Case 1 Like "Profit/Loss*" 'Error here, does not like "Like" or "Is Like" ChtTitle = "Profit/Loss" & " For " & sht.Name Case 2 Like "Evaluation*" ChtTitle = "Evaluation" & " For " & sht.Name Case 3 Like "Expenses/Revenues*" ChtTitle = "Expenses/Revenues" & " For " & sht.Name End Select With ActiveChart .ChartTitle.Characters.Text = ChtTitle End With End If Next sht End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
'Selection.FormulaArray = _ "=INDEX(sheet2!R21C15:R41C15,MATCH(1,(sheet2!R21C12:R41C12=R[-4]C2)*(sheet2!R21C13:R41C13<=R17C)*(sheet2!R21C14:R41C14>=R17C),0))"
Dim sh As Worksheet Dim r1, r2, r3, r4 Set sh = Sheets("sheet1") Set r1 = sh.Range("L21:L41") Set r2 = sh.Range("M21:M41") Set r3 = sh.Range("N21:N41") Set r4 = sh.Range("O21:O41") Dim lEmp, dDate lEmp = "983467" dDate = CDate("03/04/11") Dim v With Application.WorksheetFunction v = .Index(r4, .Match(1, (r1 = lEmp) * (r2 <= dDate) * (r3 >= dDate), 0)) End With End SubThanks
VB:i was wondering if anyone knew a way to add more than one condition to a "like" statement- such as..."*Pass*" 'and then.. If passcheck = True Then totalpasscount = totalpasscount + 1 End IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sub combine() 'This will copy data from all sheets of the selected workbooks 'To a sheet named 'Data' in the sheet in which the macro is run from Dim ObjWBSource As Workbook Dim ObjWBTarget As Workbook Dim ObjWSData As Worksheet Dim ws As Worksheet Dim pasterow As Long mainsheetname = ActiveWorkbook.Name MsgBox ("Please select spreadsheets to combine") filestoopen = Application.GetOpenFilename(MultiSelect:=True) responseval = MsgBox("Do you want to leave the combined spreadsheets open?", vbYesNo) Set ObjWBTarget = ActiveWorkbook Set ObjWSData = Worksheets("Data") 'open workbooks For Each w In filestoopen Set ObjWBSource = Workbooks.Open(Filename:=w) 'copysheetname = ActiveWorkbook.Name 'copy and paste sheets For Each ws In ObjWBSource.Sheets pasterow = ObjWBTarget.Worksheets(ObjWSData.Name).UsedRange.Rows.Count ObjWBSource.Sheets(ws.Name).UsedRange.Copy (ObjWBTarget.Worksheets(ObjWSData.Name).Range("A" & pasterow)) Next If responseval = 7 Then Application.DisplayAlerts = False ObjWBSource.Close Application.DisplayAlerts = True End If Next w End Sub
Private Sub cmdAdd_Click() Dim lRow As Long Dim lPart As Long Dim iRow As Long Dim wss As Worksheet Set wss = Worksheets("PK1") lRow = wss.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1 lPart = Me.cbProviders.ListIndex If Trim(Me.cbProviders.Value) = "" Then Me.cbProviders.SetFocus MsgBox "Please enter provider name." Exit Sub End If If Trim(Me.cbProviders.Value) = "Choose" Then Me.cbProviders.SetFocus MsgBox "Please enter provider name." Exit Sub End If If Trim(Me.txtBillNo.Value) = "" Then Me.txtBillNo.SetFocus MsgBox "Please enter Bill No." Exit Sub End If With wss .Cells(lRow, 3).Value = Me.cbProviders.Value .Cells(lRow, 2).Value = Me.txtDate.Value .Cells(lRow, 4).Value = Me.txtBillNo .Cells(lRow, 5).Value = Me.txtRetail .Cells(lRow, 6).Value = Me.txtRetail - Me.txtRetail * 0.152542373 .Cells(lRow, 7).Value = Me.txtWholesale .Cells(lRow, 8).Value = Me.txtWholesale - Me.txtWholesale * 0.152542373 .Cells(lRow, 9).Value = Me.txtRetail - Me.txtRetail * 0.152542373 - Me.txtWholesale - Me.txtWholesale * 0.152542373 'this is a problem. i get different result End With Me.txtBillNo.Value = "" Me.cbProviders.Value = "Choose" Me.txtRetail.Value = "" Me.txtDate.Value = Now() Me.txtWholesale = "" End Sub
VB:So I tried Data>Validation>Custom and entered =ISLIKE(B3,"MUT*") in the formula box but I get a message as if Excel was trying to reach some named range...ISLIKE = arg Like pattern End FunctionIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:This shows multiple pagebreaks within the same worksheet. How to combine all the pageviews in the worksheet into one single pageview? It does not matter how small the cell content looks. I just want to fit it into one pageview!Workbook . . . wk.Activate ActiveWindow.View = xlPageBreakPreviewIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:Select DateOfBirth, Name From AnimalTable WHERE PlaceOfBirth = ' " & USA & " ' OR PlaceOfBirth = ' " & Brazil & " ' AND Age