VB: Sub WriteFile() Dim oFSObj As Object, oTSStream As Object Dim lngRow As Long, lngCol As Long Dim rngeRangeToWrite As Range Dim strTextFile As String, strDelimiter As String 'Choose your delimiter here. Can be anything you want strDelimiter = "," 'You can either use the 'Used range', or use something like Set rngeRangeToWrite = Range("E2:G10") 'Set rngeRangeToWrite = ActiveSheet.UsedRange 'Create a file system object and use it to create a new 'text file. strTextFile = "C:myfile.txt" Set oFSObj = CreateObject("Scripting.FilesystemObject") Set oTSStream = oFSObj.CreateTextFile(strTextFile, True) 'This loop is for looping from the top of the sheet to the bottom For lngRow = 1 To rngeRangeToWrite.Rows.Count 'This loop will write one cell at a time for the current row For lngCol = 1 To rngeRangeToWrite.Columns.Count oTSStream.Write Chr(34) & rngeRangeToWrite.Cells(lngRow, lngCol) & _ Chr(34) & strDelimiter Next lngCol 'The vbCr character denotes the end of a text file oTSStream.Write vbCr Next lngRow oTSStream.Close End Sub If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesMany Thanks for taking the time to look at my post
=Tickers!T16/Tickers!T17In my VBA code, I'm substituting several variables in when trying to construct and assign the formula. My code looks like this:
ActiveCell.FormulaR1C1 = "=Tickers!T" & right(currCell, holdlen) & "/Tickers!T" & nextCell"right(currCell, holdlen)" contains the text string "16", and "nextCell" contains the test string "17". So when I join it all together, I should get the final string "=Tickers!T16/Tickers!T17", but instead it produces :
=Tickers!'T16'/Tickers!'T17'... with little single quotes around the T16 and T17. This causes a #NAME? error in the cell. Would anyone have any idea what I'm doing wrong, and how to get those little single quotes out of there?
Sub test() Dim myvariable As String myvariable = ActiveSheet.Cells(1, 1).Formula MsgBox myvariable End SubThis is stripping out the initial single quote from any comment cells.
VB: =Find(A1,""") If you like these VB formatting tags please consider sponsoring the author in support of injured Royal MarinesI need to identify all cells with text strings that contain a single quotemark. I just want to make a list of all part numbers that have a single quotemark in their description (ie sheet, 2"x4").