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
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:Problem 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?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 iIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:I 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?Group1 blah blahIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:I was thinking of using the "Is Like" operator to determine which files should be opened but am unsure of the syntax.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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Private Sub TextBox1_Change() 'the change event runs each time the user 'types into a text box Dim s As String Dim i As Integer s = TextBox1.Text 'Note the use of the ListIndex property of the ListBox 'If the ListIndex is -1 means nothing selected 'If 0 means the first item selected ListBox1.ListIndex = -1 If TextBox1.Text = "" Then 'nothing typed Exit Sub End If For i = 0 To ListBox1.ListCount - 1 'use the LIKE operator to compare 'convert both to Uppercase as well so case does not matter If UCase(ListBox1.List(i)) Like UCase(s & "*") Then ListBox1.ListIndex = i Exit Sub End If Next End SubPlease help me.