excel variable sheet number equal to column number reference-CodePudding

For my latest bright idea I need to change the reference of variable sheet to the next column in another sheet. I create a copy of an existing sheet ("INL") so I get ("INL(2)),("INL(3)"),....,("INL(n)"). The references in these sheets should be updated to the next column in sheet ("Info") so that the formulas in ("INL B:B") reference to ("Info C:C), ("INL (2)B:B") references to ("Info D:D")... INL (n) to Info (x).

I tried something like the code below but now I am stuck.

Private Sub CommandButton2_Click()

Application.ScreenUpdating = False
 Dim ws As Worksheet       
 Dim rng As Range
Set ws= Sheets("INL") Or ws.Name Like ("'INL (*)'")
    For Each ws In ThisWorkbook.Sheets    
      Set rng = ws.Range("C:C")
        For Each cell In rng
    cell.Formula = Replace(cell.Formula, "=Info!C", "=Info!(=COLUMN(C24)-2)")
    Next Cell
    Next ws
Application.ScreenUpdating = True
End Sub

Does anyone have a good way to handle this?

CodePudding user response：

Try:

Private Sub CommandButton2_Click()

Dim ws As Worksheet
Dim rng As Range
Dim c As Range
Dim varCopy As Variant
Dim strCol As String
    
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Sheets
    If Left(ws.Name, 3) = "INL" Then
        If ws.Name = "INL" Then
             varCopy = Array(1, 1)              'An exception case to handle 1st sheet which has no number in it.
        Else
             varCopy = Split(ws.Name, ")")      'Step 1 of getting sheet copy number.
             varCopy = Split(varCopy(0), "(")   'step 2. Copy # will be element 1 in 0 indexed array.
        End If
        
        Set rng = ws.Range("B:B")               'Question suggests this should be col B, not C. I would recommend limiting the range only to affected rows...
                                                '...rather than looping through the >1 million rows that are in each column.
        strCol = Columns(CLng(varCopy(1))   1).Address  'Column number is 1 more than sheet copy number.
        strCol = Mid(strCol, InStr(1, strCol, ":", vbTextCompare)   1, 100) 'Getting text to right of : ensures including columns AA and higher, if needed.
        
        For Each c In rng.Cells
           c.Replace What:="Info!C", Replacement:="Info!" & strCol, LookAt:=xlPart 'Changed your line to use the appropriate Replace method.
        Next c
           
    End If
Next ws

Application.ScreenUpdating = True

Set rng = Nothing
Set ws = Nothing

End Sub

CodePudding user response：

Worksheet Copy Index vs Column Number

I have assumed that the references in INL are correct and the code will only modify the references in the copies. If this is not the case, you will have to modify the code.
I have also assumed that the references are simple and relative (no $ for columns) e.g. =Info!C2.
Anyways, I have left the three consecutive Debug.Print lines in the code for you to better understand what is happening.

Option Explicit

Private Sub CommandButton2_Click()
    ReplaceReferences
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Replaces cell references...
' Calls:        IntColumnNumber,StrStringBetweenTwoChars,StrColumnString
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ReplaceReferences()
    
    Const sName As String = "INL"
    Const sCol As String = "B"
    Const sfRow As Long = 2
    Const dName As String = "Info"
    Const dfCol As String = "C"
    Const dColDiff As Long = 1 ' don't change: related to (2),(3)...
    
    Dim dNameExclam As String
    If UBound(Split(dName)) > 0 Then
        dNameExclam = "'" & dName & "'!"
    Else
        dNameExclam = dName & "!"
    End If
    
    Dim FindString As String: FindString = dNameExclam & dfCol
    
    Dim dfColNum As Long: dfColNum = IntColumnNumber(dfCol)
    
    Application.ScreenUpdating = False
    
    Dim sws As Worksheet
    Dim srg As Range
    Dim scrg As Range
    Dim swsNum As String
    Dim swsName As String
    Dim ReplaceString As String
    Dim dColString As String
    
    For Each sws In ThisWorkbook.Worksheets
        swsName = sws.Name
        If UCase(swsName) Like UCase(sName) & " (*)" Then
            swsNum = StrStringBetweenTwoChars(swsName, "(", ")")
            If IsNumeric(swsNum) Then
                dColString = StrColumnString(dfColNum   swsNum - dColDiff)
                With sws.Columns(sCol)
                    Set srg = .Resize(.Rows.Count - sfRow   1).Offset(sfRow - 1)
                End With
                Set scrg = Intersect(sws.UsedRange, srg)
                If Not scrg Is Nothing Then
                    ReplaceString = dNameExclam & dColString
Debug.Print "Range: '" & sws.Name & "!" & scrg.Address(0, 0) & "'"
Debug.Print "'" & FindString & "' to '"; ReplaceString & "'"
Debug.Print "FirstCell formula: '" & scrg.Cells(1).Formula & "'"
                    ' This may not work as expected when you will need to loop.
                    scrg.Formula = Replace(scrg.Cells(1).Formula, _
                         FindString, ReplaceString, , , vbTextCompare)
                    Set scrg = Nothing
                End If
            End If
        End If
    Next sws
    
    Application.ScreenUpdating = True

End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the Excel column number from a (column) string.
' Remarks:      Restricted only by 'ColumnNumber As Long', i.e., e.g.:
'               Debug.Print IntColumnNumber("FXSHRXW") ' = 2147483647
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function IntColumnNumber( _
    ByVal ColumnString As String) _
As Double
    Const ProcName As String = "IntColumnNumber"
    On Error GoTo ClearError
    
    Dim ColumnStringLength As Long: ColumnStringLength = Len(ColumnString)
    If ColumnStringLength = 0 Then Exit Function
    
    Dim n As Long
    Dim CharNumber As Long
    Dim CharIndex As Long
    Dim ColumnNumber As Long
    
    For n = ColumnStringLength To 1 Step -1
        CharNumber = Asc(UCase(Mid(ColumnString, n))) - 64
        If CharNumber < 1 Or CharNumber > 26 Then
            Exit Function
        End If
        ColumnNumber = ColumnNumber   CharNumber * 26 ^ CharIndex
        CharIndex = CharIndex   1
    Next
    
    IntColumnNumber = ColumnNumber

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the Excel column string from a (column) number.
' Remarks:      Restricted only by 'ColumnNumber As Long', i.e., e.g.:
'               Debug.Print StrColumnString(2147483647) ' = "FXSHRXW"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function StrColumnString( _
    ByVal ColumnNumber As Long) _
As String
    Const ProcName As String = "StrColumnString"
    On Error GoTo ClearError
    
    Dim ColumnString As String
    Dim Remainder As Long
    
    Do
        Remainder = (ColumnNumber - 1) Mod 26
        ColumnString = Chr(Remainder   65) & ColumnString
        ColumnNumber = Int((ColumnNumber - Remainder) \ 26)
    Loop Until ColumnNumber = 0

    StrColumnString = ColumnString

ProcExit:
    Exit Function
ClearError:
    Debug.Print "'" & ProcName & "' Run-time error '" _
        & Err.Number & "':" & vbLf & "    " & Err.Description
    Resume ProcExit
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Returns the string between two characters exclusively.
' Remarks:      Only the first occurrence of the characters is considered.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function StrStringBetweenTwoChars( _
    ByVal SearchString As String, _
    ByVal FirstChar As String, _
    ByVal SecondChar As String) _
As String
    
    Dim fcPos As Long: fcPos = InStr(1, SearchString, FirstChar, vbTextCompare)
    If fcPos = 0 Then Exit Function
    Dim scPos As Long: scPos = InStr(1, SearchString, SecondChar, vbTextCompare)
    If scPos <= fcPos Then Exit Function
    
    StrStringBetweenTwoChars _
        = Mid(SearchString, fcPos   1, scPos - fcPos - 1)

End Function