VB: Sub ChartTitles() 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 Sub If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesThank you
VB: Sub ColoringDeleting() Dim WSHT As Worksheet, i As Long, Lastrow As Long Set WSHT = ActiveSheet i = 2 Lastrow = WSHT.Range("A" & wsh.Rows.Count).End(xlUp).Row While i
Sub summarize_cbu() Dim counter As Long Dim counter2 As Long For counter = 2 To 787 For counter2 = 6 To 296 If Sheet1.Cells(counter, 1) Like Sheet2.Cells(counter2, 3) Then Sheet1.Cells(counter, 2) = Sheet1.Cells(counter, 2) + Sheet2.Cells(counter2, 11) Else End If Next Next End SubExample value assignment.
If "Sample Value" Like "cbu-sample value" Then next line here must be executed..but it executed directly to else else end ifThank you for helping!
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?
Range("F10:F30").Select Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""TRG""" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Font .Bold = True .Italic = True .TintAndShade = 0 End WithPrior to formatting I would like to test the value of the cell if it contains "TRG" as the first three characters and then format "Bold = True and Italic = True.
i = 1 Do Until i > Len(txtSearchName) If Not Mid(txtSearchName, i, 1) Like "[A-Z,a-z,-,',., ]" Then MsgBox "Search name is invalid. You must enter valid search name to proceed." & vbCrLf & "Please try again.", vbOKOnly txtSearchName.SetFocus NameBadFlag = True Exit Do End If i = i + 1 LoopThe above code allows all the characters, including apostrophe, period, and space, but not a hyphen. What gives? Thanks for any help you can lend.
For b = 3 To 10000 If Range("A" & Trim(Str(b))).Value = "" Then Exit For End If If ((Not Range("A" & Trim(Str(b))).Value Like "*Deploy*") And _ (Not Range("A" & Trim(Str(b))).Value Like "*Pre-prod*")) Then Range("A" & Trim(Str(b)).Select Selection.Delete Shift:=xlUp b = b - 1 End If Next
Function getname(rng As Range) As String Dim getcaps As String For x = 1 To Len(rng) If Mid(rng, x, 1) Like "[ABCDEFGHIJKLMNOPQRSTUVWXYZ-]" And Not Mid(rng, x + 1, 1) Like "[a-z]" Then If Mid(rng, x + 1, 1) <> " " Then getcaps = getcaps & Mid(rng, x, 1) Else getcaps = getcaps & Mid(rng, x, 1) & " " End If End If Next getname = Trim(Mid(rng, Len(getcaps), Len(rng))) & " " & Trim(Mid(getcaps, 1, Len(getcaps))) End Function
VB: Dim i As Integer Dim mybook As String Dim myfile As String Dim mst As Workbook Set mst = ActiveWorkbook mybook = ActiveWorkbook.Path For i = 1 To 6 myfile = ("*Stack " & i & ".xlsx") Workbooks.Open Filename:=(mybook & myfile) On Error Goto 0 Next i If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesProblem is it keeps giving me a 1004 error saying that C:Users*Stack1.xlsx cannot be found. Do I have to use an array or the Like operator(which I couldn't get to work as it kept returning Boolean results). Any thoughts?
VB: For Each NamedRange In Group1 blah blah If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesI was eyeing the "Like" operator, but I'm not sure if that's the right one, or how I'd go about it. Any ideas?
VB: Sub Openfilesfromlist() Dim i As Integer Dim wbResults As Workbook Dim BookName As String Dim filename As String On Error Resume Next With Application.FileSearch .NewSearch .LookIn = "G:5762x-Reconciliation57621-LON ReconciliationBrokerageFX-MM" .FileType = msoFileTypeExcelWorkbooks 'i want the file name to be determined by a list of names beginning In cell a1 'of the open workbook, not including paths- how can i combine this with the "IsLike" operator?? 'the filenames will not be exactly the same as the cell value's but will contain key words 'such as "Garban" .filename = Cell.Value If .Execute > 0 Then 'Workbooks in folder For i = 1 To .FoundFiles.Count 'Loop through all 'Open Workbook x and Set a Workbook variable to it Set wbResults = Workbooks.Open(.FoundFiles(i)) 'i will be adding code here to work with each opened work book later Next i End If End With On Error Goto 0 End Sub If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesI was thinking of using the "Is Like" operator to determine which files should be opened but am unsure of the syntax.