Posts by lizzie5

I am! I need to do some additional testing on my full dataset to be sure and I'll let you know if any issues, but I don't anticipate any...your sample looks perfect!!

Wow! What a formula...I don't know if I understand it but it looks correct! Haha...This is fantastic...thanks so much for your help and for doing it so quickly!

Hi, thanks for looking...it appears they are in ascending order, I wasn't sure it was guaranteed but that looks to be correct!

Hi – I need some assistance in extracting information from a dynamic string, either using VBA or formulas. In one column is an “id” and the adjacent column has a string of “Sale Types” – each Sale Type has a date/time and a title. I want to extract a date and time from the Sale Type that meet these conditions:

• Sales Type name contains “STARTING”
• If multiple names containing “STARTING” are found, only output the earliest date

One example Sales Type string:

[{"date":"2020-01-17T23:00:00Z","name":"Official STARTING"},{"date":"2020-01-17T23:00:00Z","name":"VIP Package Onsale"}{"date":"2020-01-19T23:00:00Z","name":"STARTING Onsale"}]

In the example above, there are two Sales Types with STARTING in the name, but I just want the earliest date, so my output would be: 1/17/2020 3:00 pm

I’ve also attached a sample file with more examples if that helps. Note on the times (3:00pm, etc.) - I’m unsure of the exact time zone the strings are using, but I can always convert time zone later as long as the data is extracted.

Thank you so much for your time!

Re: VBA code to search txt file and display specific results in MsgBox

Thanks cytop.

Re: VBA code to search txt file and display specific results in MsgBox

Wow, thank you very much! This looks like it works great. I have a question regarding this part of the code and your note:

If InStr(vData(lngLine), strSearch) > 0 Then
             '// Update final message - but only so many. A messagebox is only so big
             
            If lngfound < 21 Then
                strMsg = strMsg & "Line: " & Right("00000" & CStr(lngLine), 5) & vbTab & vData(lngLine - 2) & vbCrLf
            End If
            lngfound = lngfound + 1
        End If

Does the "<21" mean it will only account for up to 21 occurrences of finding that string, like you mentioned in your note about the messagebox is only so big?

In my case, while it would be rare to see so many matches, there would never be more than 35 instances of the found message appearing, so I am assuming I can change "<21" to "<36"?

Thanks again.

Hello - I need help using VBA to search a txt file for specific text, and then if that text is found to display text from 2 rows above in a message box. I have found code to search a text file - see URL source below, as well as the code. The code works ok: it searches my text file for specific text ("COMMAND TEMPORARILY DISABLED"), and then it will display the line/row that that text is found in. I added code to the message box prompt to display the row number 2 rows above. What I am missing is code to specify that I want to see what's in the text file 2 rows above the found text. I don't care about the row numbers, I want to see what text the row contains.

I have attached a text file here. The rows I'm looking for are two rows above the found text, which in the attached example are:
** NEXT (ARZ) ?? REPGEN
** NEXT (NY2) ?? REPGEN

I'm actually specifically looking for the 3 characters in the parenthesis (i.e. ARZ & NY2), but if that can't be done it's fine to display the entire row's contents.

Lastly, I'd want to ensure the entire document is searched for the text I'm searching for, so if there is more than 1 result found it will display all of them, and not exit if/when one match is found.

I hope this makes sense. I have been scouring the web trying to find a way to output the found row's contents rather than the row number, but I can't find it. Help would be greatly appreciated!!

Sub SearchTextFile()
'https://social.msdn.microsoft.com/Forums/en-US/62fceda5-b21a-40b6-857c-ad28f12c1b23/use-excel-vba-to-open-a-text-file-and-search-it-for-a-specific-string?forum=isvvba


'file path
Dim Path As String
Path = Sheets("master list").Range("M1").Value
Dim strFileName As String
strFileName = Path & "Dropbox\I6 - Canada\I6 Error Log\1Host Output\I6HOST.TXT"




    Const strSearch = "COMMAND TEMPORARILY DISABLED" 'text I am searching the I6HOST.TXT. file for
    Dim strLine As String
    Dim f As Integer
    Dim lngLine As Long
    Dim blnFound As Boolean
    Dim HostLine As Integer
    Dim HostText As String
    Dim HostResult As String




    f = FreeFile
    Open strFileName For Input As #f
    Do While Not EOF(f)
        lngLine = lngLine + 1
        Line Input #f, strLine
        If InStr(1, strLine, strSearch, vbBinaryCompare) > 0 Then
            HostLine = lngLine - 2  'the line of data I need is 2 lines above the lngLine where the search string is found
            MsgBox "Search string found in line " & lngLine & ". 3 character system name is in line " & HostLine, vbInformation 'MsgBox works fine, but I want it to show me the data in the HostLine (not tell me what row/line it is)
             blnFound = True
            Exit Do
        End If
    Loop
    Close #f
    If Not blnFound Then
        MsgBox "Search string not found", vbInformation
    End If
    
End Sub

Hi - I need help opening a file, delimiting by tab, and importing the data as text (not general). I have code that I'm using now that works fine, but now I need to expand the code to a dynamic number of columns.

The code I'm using now only imports some of the data as text, but I can go ahead an apply Text to all columns to make it simpler. As you can see below my Array applies to a specific amount of columns (29 total).

Workbooks.OpenText filename:=Path & "Desktop\I6 FILES\Output\" & host1 & "\" & file1 & ".001" _
        , DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 2), _
        Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 2), Array(17, 2), Array(18, 2), Array(19, 1), Array(20, 1), Array(21, 2), _
        Array(22, 2), Array(23, 1), Array(24, 2), Array(25, 1), Array(26, 2), Array(27, 2), Array( _
        28, 2), Array(29, 2)), TrailingMinusNumbers:=True

I need to modify this code to expand to any number of columns. I will always have a minimum of 29 columns, but I could have any maximum number. I know that I can go in and hard-code the array numbers that I think will cover my total possible columns, but I'm sure there's a way to do it dynamically...Can anyone help? Thanks.

Re: Autofill formula that references two columns

Thank you so much Derk!! This does exactly what I need it to.

I would really appreciate help on how to autofill this formula in my spreadsheet! I've been trying to look online for an answer but I can't find what I'm looking for

I THINK this can be accomplished by the INDEX formula but I don't know enough about it.

The first two "sets" of the formula are below. The formulas reference data from another sheet in my workbook (titled 'TO PRINT') that has data in columns A & B. Each formula "set" contains two lines, which both reference the same row, however each line references a different column (A or B). I want to be able to drag this down so the next formula "set" would reference A4 and B4, followed by A5 and B5, etc. Right now if I try to drag this down it skips rows, it does not autofill consecutively.

[TABLE="width: 2018"]

=IF('TO PRINT'!A2="","",CONCATENATE("SwapUDP:" & 'TO PRINT'!A2))

=IF('TO PRINT'!B2="","",CONCATENATE("Get:C:\Users\test\Desktop\COMM TESTING\" & 'TO PRINT'!B2 & ".001"))

=IF('TO PRINT'!A3="","",CONCATENATE("SwapUDP:" & 'TO PRINT'!A3))

=IF('TO PRINT'!B3="","",CONCATENATE("Get:C:\Users\test\Desktop\COMM TESTING\" & 'TO PRINT'!B3 & ".001"))

[/TABLE]

Re: Automate adding conditional formatting to multiple non-consecutive cells

Any takers?

Hi, I’m trying to figure out how to automate adding conditional formatting to a big range of cells. The cells are not consecutive, but rather 7 rows apart. The conditional formatting should look to a specific cell, and highlight any cell in the specific range that does NOT equal the specific cell.

For example, cell O14 is the cell that all the other cells in row O should look to. Starting with O17, then O24, O31, etc….all the way through O2117 – I need conditional formatting in those specific cells to highlight if the value within that cell <> O14.

The same thing applies to column P (P14 is the reference cell for P17, P24….P2117), and every column all the way through column AL (AL14 is the reference for AL17, AL24…AL2117).

In case additional background helps: All the values in Columns O-AL / Row 14 refer to a Facility Fee. The cells below each reference cell in row 14 are a pricing grid, and each set of prices has a facility fee. I want to call out any values that may not be equal to those in row 14.

I recorded a macro adding the formatting for the first few cells in column O, but I don’t know how to apply it to all of the ranges I need:

Range(“O17,O24”).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
        Formula1:="=$O$14"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.499984740745262
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions(1).StopIfTrue = False

Can anyone help? Let me know if you need more information. Thanks!

Re: Find text in worksheet and copy adjacent value to another worksheet

Hi, it looks like the code that you provided in post #4 works great! The only change I made was to add the xlCellTypeVisible to LookInR so it skipped hidden rows in the search. Thank you so much, I really appreciate it!!

Re: Find text in worksheet and copy adjacent value to another worksheet

Hi, are you saying to replace that code with the last part of mine? So the code would now look like this:

Dim Cell As Range
    Set Cell = Sheets("Offer Setup Tracker").Range("A:A").SpecialCells(xlCellTypeVisible).Find(What:="PROCESSING DATE:", _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
Dim NextRow As Long
    NextRow = Sheets("2013 Totals").Range("O65536").End(xlUp).Row + 1




    If Not Cell Is Nothing Then
        Range(Cell.Offset(0, 1), Cell.Offset(0, 1)).Copy Destination:=Sheets("2013 Totals").Cells(Rows.Count, "O").End(xlUp).Offset(1, 0)
        
    End If

Let me know if I misinterpreted, but if that's what you meant it looks like that's doing the same thing as my original code was. The output on the "2013 Totals" tab is still just showing the first Processing Date found, it's not going through the worksheet and finding all matches.

Hi, I have been trying to figure this out on my own by looking through the forums but I haven't been able to get my code to work the way I want to. My code below IS working but it is stopping at the first value it finds. I need it to continue searching the worksheet until it finds all matching values. The worksheet will be constantly changing so the code/search needs to be dynamic.

Here is what I'm trying to do:

• Search all visible rows in column A on worksheet "Offer Setup Tracker" for text "PROCESSING DATE:"
• If it finds "PROCESSING DATE:", copy the adjacent cell value in column B
• Paste that copied value in the next available row in column O on worksheet "2013 Totals"

I have attached my file here if it helps. I also put my desired result (a list of dates) in column O on the "2013 Totals" tab.

I have my code in the Workbook_Open slot because I want it to run every time I open the workbook.

Can anyone help? Thanks very much.
forum.ozgrid.com/index.php?attachment/51508/

Dim Cell As Range
    Set Cell = Sheets("Offer Setup Tracker").Range("A:A").SpecialCells(xlCellTypeVisible).Find(What:="PROCESSING DATE:", _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
Dim NextRow As Long
    NextRow = Sheets("2013 Totals").Range("O65536").End(xlUp).Row + 1




    If Not Cell Is Nothing Then
        Range(Cell.Offset(0, 1), Cell.Offset(0, 1)).Copy Destination:=Sheets("2013 Totals").Range("O" & NextRow)
    End If

Re: Workaround for dragging down formulas in groups of 3

Hi Gadi. Thanks for your response. I also just found that a similar function works as well, using INDEX (sample below). I don't quite understand it, but it works!

=INDEX('Prep Autype'!$O$1:$Q$250,1+INT(ROW('Prep Autype'!Q1)/3.0001),ROW('Prep Autype'!Q1)-INT(ROW('Prep Autype'!Q1)/3.0001)*3)

Thank you!

Hi. I’m having trouble dragging down some formulas in my worksheet, and I’m hoping there is a better workaround. Please see attached example.

On my tab “AUT-NO TABLE” are formulas that refer to data on the “Prep Autype” tab. I need to drag down formulas on the “AUT-NO TABLE” tab in groups of 3 rows: the first 3 rows all refer to cells in row 1 of the “Prep Autype” tab, the next 3 rows refer to cells in row 2 of the “Prep Autype” tab, etc. When I attempt to drag down in groups of 3 rows, the formulas move down in increments of 3, but I need increments of 1.

The only thing I can do now is use Find & Replace to modify the formulas to look at the correct row. I need these formulas to drag down to reference 100s of rows on the “Prep Autype” tab, so Find & Replace each time would obviously take a lot of manual work. Is there anything else I can do here?

I have pasted the first few reference formulas from the spreadsheet below for a quick visual. As you can see, they are in groups of 3 and I need to drag down to reference rows in increments of 1:

[TABLE="width: 1404"]

=UPPER(LEFT('Prep Autype'!$B1,16))

=(UPPER(CONCATENATE(LEFT('Prep Autype'!$E1,LEN('Prep Autype'!$E1)-5),": ",LEFT('Prep Autype'!$C1,57))))

='Prep Autype'!$L1

=UPPER(LEFT('Prep Autype'!$B2,16))

=(UPPER(CONCATENATE(LEFT('Prep Autype'!$E2,LEN('Prep Autype'!$E2)-5),": ",LEFT('Prep Autype'!$C2,57))))

='Prep Autype'!$L2

=UPPER(LEFT('Prep Autype'!$B3,16))

=(UPPER(CONCATENATE(LEFT('Prep Autype'!$E3,LEN('Prep Autype'!$E3)-5),": ",LEFT('Prep Autype'!$C3,57))))

='Prep Autype'!$L3

[/TABLE]

Thanks for your time!!!

Re: Expand macro that removes special characters to run through entire column

Many thanks to BOTH of you for the quick replies! Both macros work great...much appreciated!

Hi all, I'm hoping to get some help expanding a REALLY useful macro I found in another thread. The code below takes a value and removes any special characters (including spaces) and outputs the result in the neighboring column.

Sub SpecialCharacters()'removes all special characters from contents of column A, INCLUDING spaces
'outputs value in column B




Dim a$, b$, c$, i As Integer
     
a$ = Range("A1").Value
    For i = 1 To Len(a$)
        b$ = Mid(a$, i, 1)
        If b$ Like "[A-Z,a-z,0-9]" Then
            c$ = c$ & b$
        End If
    Next i
Range("B1").Value = c$




End Sub

I can't figure out how to expand the macro to be able to run through any/all values in column A. Currently, as you can see, the macro refers to specific cells and takes the value of A1, then outputs the result into cell B1. My column A length will vary and I need this code to be able to run through column A as long as there is a value there, and then output the result into the corresponding cell in column B.

Hopefully I made sense here. I attached a sample document illustrating what I'm asking for, the doc also includes the code posted here. Thanks to all for your help!!

Re: VBA Maco Code Taking Long Time To Run

Thank you both for your suggestion. Dave's suggestion runs the fastest, but it seems to cause the result to output incorrectly. The value in cell AF1 is a formula, not sure if that is affected by the CalcuationManual part of the code (I'm not too familiar with that yet).

I tried trunten's code and it's pretty quick and a HUGE improvement from my original code.

Thanks!!!