Noob To R1C1 format, New Formula Received and I Need Help Please.

Hi There,

I am not so new to VBA in Excel but I will profess to be a noob when it comes to the R1C1 format in formulas.

The following lines have me stumped, because they only get part of the data I need. Can someone possibly interpret them for me please and let me know how to increase the amount of data each one takes from the origination locatio? I would be eternally grateful, as I can then tell the Division Chief that I was able, with some assistance from my friends, to successfully modify the macro for our personal use. Thank you to all who respond. Here is the code that has me cornfused

    Range("D2").FormulaR1C1 = "=IF(AND(MID(RC[-3],2,1)=""*"",MID(RC[-3],3,1)<>""*""),RC[-3],R[-1]C)"
    Range("E2").FormulaR1C1 = "=IF(AND(LEFT(RC[-4],1)=""*"",MID(RC[-4],2,1)<>""*""),RC[-4],R[-1]C)"

So, those are the lines that have been giving me fits since last week. I've tried debug.print statements, but just can't seem to figger this one out and was hoping one of ya'll could help.

Thanks again.

Hopelessly lost in Alabama

Well, they work, sort of...yes, searching for the character "*" because it identifies a certain type of record in the raw, exported data. Depending on how many "*" there are, tells you the type of data. But, they aren't picking up the entire field in exported, raw data.

This excel macro file supposedly takes a text file and massages the raw data, which was exported from our financial system (an SAP ERP), then the macro saves the raw data, after massage, as an Excel file. Which is then run thru an MS Access macro, which further massages the data, (picking up some detailed information by matching fields in tables already set up in Access).

Then, at the last, there is a pivot table excel file, that is supposed to show you your data in whatever manner you wish, depending on how you set up the pivot table. The Sum fields are already set, the end user just brings down the data fields that uniquely identify the summed amounts.

Not that it matters but I work as a Financial Analyst for the Government, so that should tell you the importance of what I am trying to do, and I am not trying to influence anyone, was just stating my position and why I am asking these questions.

We were given these tools by a vendor at a conference and I am, (since I am the only Finanacial Analyst where I work that used to be a Software Engineer, but its been a long time since I have done any programming), tasked with seeing if these tools can help us in reporting the Status of the Funds allocated to us by Congress.

The two lines in question are part of a macro:

Sub ProcessZFSC5(ByVal strName As String, Optional blnTimer As Boolean = True)
    Dim start As Date
    Dim strNewFile As String
    
    strNewFile = Left(strName, Len(strName) - 4) & ".xlsx"
    strNewName = Right(strNewFile, Len(strNewFile) - InStrRev(strNewFile, "\"))
'    Dim headers As Variant
'    headers = Array("", "Funds Center", "Fund", "Functional Area", "Funded Program", "Commitment Item", "      AFP      ", "   Allotment   ", "  Commitment   ", "  Obligation   ", "   Expenses    ", " Disbursement  ", "Available Allot", "   Avail AFP   ")
    
    start = Now
    Application.ScreenUpdating = False
    Workbooks.Add
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=strNewFile, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
    Application.DisplayAlerts = True
    Workbooks(strNewName).Close
    Workbooks.Open Left(strName, Len(strName) - 4) & ".xlsx "
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & strName, Destination:=Range("$A$1"))
        .Name = "ZFSC5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Do While Cells(1, 2) = ""
        If Left(Trim(Cells(1, 1)), 19) = "Date of Selection: " Then
            datRan = Cells(1, 1)
        End If
        Rows("1:1").Delete
    Loop
    Columns("A:A").Delete
    Rows("2:3").Delete Shift:=xlUp
    Range("A1").Select
    
    Columns("B:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").FormulaR1C1 = "Funds Center"
    Range("C1").FormulaR1C1 = "Fund"
    Range("D1").FormulaR1C1 = "Functional Area"
    Range("E1").FormulaR1C1 = "Funded Program"
    Range("F1").FormulaR1C1 = "Commitment Item"
    Range("G1").FormulaR1C1 = "Date Run"
    
    Columns("B:G").NumberFormat = "General"
    'Columns("G:G").NumberFormat = "@"
    x = ActiveSheet.UsedRange.Rows.Count
    Range("B2").FormulaR1C1 = "=IF(MID(RC[-1],4,1)=""*"",RC[-1],R[-1]C)"
    Range("C2").FormulaR1C1 = "=IF(AND(MID(RC[-2],3,1)=""*"",MID(RC[-2],4,1)<>""*""),RC[-2],R[-1]C)"
    Range("D2").FormulaR1C1 = "=IF(AND(MID(RC[-3],2,1)=""*"",MID(RC[-3],3,1)<>""*""),RC[-3],R[-1]C)"
    Range("E2").FormulaR1C1 = "=IF(AND(LEFT(RC[-4],1)=""*"",MID(RC[-4],2,1)<>""*""),RC[-4],R[-1]C)"
    Range("F2").FormulaR1C1 = "=IF(LEFT(RC[-5],1)<>""*"",RC[-5],R[-1]C)"
    Range("G2").FormulaR1C1 = "=R[-1]C"
    Range("B2:G2").AutoFill Destination:=Range("B2:G" & x)
    
    Range("G2").FormulaR1C1 = Replace(Replace(datRan, "    Date of Selection:  ", ""), " Time of Selection: ", "")
    Columns("B:G").Copy
    Columns("B:G").PasteSpecial Paste:=xlPasteValues
    Columns("G:G").NumberFormat = "m/d/yy h:mm:ss;@"
    Application.CutCopyMode = False
    
    ActiveSheet.Range("$A$1:$O$" & x).AutoFilter Field:=1, Criteria1:="=~**", Operator:=xlAnd
    
    For i = x To 2 Step -1
        If Rows(i & ":" & i).Hidden = False Then
            Rows(i & ":" & i).Delete
        End If
    Next
    Selection.AutoFilter
    Range("A1").Select
    Columns("A:A").Delete Shift:=xlToLeft
    
    ActiveSheet.Range("$A$1:$N$" & x).AutoFilter Field:=7, Criteria1:="="
    ActiveSheet.Range("$A$1:$N$" & x).AutoFilter Field:=8, Criteria1:="="
    ActiveSheet.Range("$A$1:$N$" & x).AutoFilter Field:=9, Criteria1:="="
    ActiveSheet.Range("$A$1:$N$" & x).AutoFilter Field:=10, Criteria1:="="
    ActiveSheet.Range("$A$1:$N$" & x).AutoFilter Field:=11, Criteria1:="="
    ActiveSheet.Range("$A$1:$N$" & x).AutoFilter Field:=12, Criteria1:="="
    
    Rows("2:2").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    
    Cells.Replace What:="~*", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    For i = 1 To 5
        For j = 2 To ActiveSheet.UsedRange.Rows.Count
            Cells(j, i) = Trim(Cells(j, i))
            If InStr(1, Cells(j, i), " ") Then Cells(j, i) = Left(Cells(j, i), InStr(1, Cells(j, i), " ") - 1)
        Next
    Next
    
    Columns("A:E").NumberFormat = "@"
    Cells.EntireColumn.AutoFit
    ActiveWorkbook.Close True
    If blnTimer Then MsgBox "Process took " & convertTimer(DateDiff("s", start, Now)) & " to run."
    Application.ScreenUpdating = True
End Sub

No, I don't expect anyone to plow through that listing, nor make any changes to it, I am just trying to understand why those lines are not getting all of the data in the field. Since this is the first macro that hits the raw data, it has to happen here, because it is present before this macro, and gone afterwards...

Thanks.

Thanks anyways people, I do appreciate all input. However, still confused, but will try to muddle through this on my own....I just can't see where those data fields are truncated in the transition between the raw text file and the new excel spreadsheett....

Alan,

Thank you for the response. Is there a way in VBA using the immediate window, to change formula's from the R1C1 format, to the other format where you specify the cell like A1, or C10?

I thought I heard or read somewhere that you could do that, but I cannot remember, nor can I find it searching on the net....but I'm not an expert in searching either....

Thanks again.

Derek in Alabama