Column based on header in excel vba

Question

The formula:

=IF(RC[2]=""Debit"",RC[-1],IF(RC[2]=""Credit"",-RC[-1]))

Says it will check if the value is debit/credit in col M and put (-) in col K.

My question is what if we don't know that debit/credit is in col M only? What we will give instead of RC[2]? We Only know that header of that column will be "Debit or Credit".

My full code:

Rows("1:1").Select
 Selection.Find(What:="AMNT", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Select

  ActiveCell.Offset(1, 0).Select 'Noting but K2
  Range(Cells(2, ActiveCell.Column), Cells(lastRow, ActiveCell.Column)).FormulaR1C1 = _
    "=IF(RC[2]=""Debit"",RC[-1],IF(RC[2]=""Credit"",-RC[-1]))"
ActiveCell.EntireColumn.Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
  Application.CutCopyMode = False

Answer 1

Your code does a lot of selecting, and nothing with those selections. At the best of times it is best to avoid using select as it slows down your code (and in most cases is unnecessary).

This code assumes the worksheet is Sheet1, if not change the sheet reference to suit your own Worksheet.

I create variables for all the numbers and ranges I want to use which makes the code easier to read and follow (as the variable can be descriptively named).

I find the last column in row 1 (assuming this is the header row) which means the code will work all the same if columns are added or removed.

Once the column header is found we assign the credit or debit column number to DebtCreditColumn and use that to define our HeaderRange.

We then do the same for AMNTColumn.

I added a couple of If...Then statements to display a MsgBox and abort the code if either values are 0 (which means the headers weren't found).

Then minus AMNTColumn from DebtCreditColumn to get the difference and assign to FormulaReferenceColumn.

Then find the LastRow in the Debit or Credit and set our TargetRange for the 'AMNT Column' from row 2 to the LastRow (LastRow wasn't defined in your code so I assumed it was the 'Debit or Credit' column).

Finally incorporate the FormulaReferenceColumn into our formula to be written to our TargetRange.

Like so:

Sub ParanTest()

Dim DebtCreditColumn As Long
Dim AMNTColumn As Long
Dim LastColumn As Long
Dim FormulaReferenceColumn As Long
Dim LastRow As Long
Dim HeaderRange As Range
Dim TargetCell As Range
Dim TargetRange As Range

With Sheet1
    LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
    Set HeaderRange = .Range(.Cells(1, 1), .Cells(1, LastColumn))
End With

For Each TargetCell In HeaderRange
    If TargetCell.Value Like "Debit or Credit" Then
        DebtCreditColumn = TargetCell.Column
        Exit For
    Else
        'Go To Next Cell
    End If
Next TargetCell

For Each TargetCell In HeaderRange
    If TargetCell.Value Like "AMNT" Then
        AMNTColumn = TargetCell.Column
        Exit For
    Else
        'Go To Next Cell
    End If
Next TargetCell

'In case the column can't be found, this will notify you and abort the code to avoid errors.
If DebtCreditColumn = 0 Then
    MsgBox "A column header 'Debit or Credit' could not be found.", vbOKOnly, "No column found!"
Exit Sub
End If

'In case the column can't be found, this will notify you and abort the code to avoid errors.
If AMNTColumn = 0 Then
    MsgBox "A column header 'AMNT' could not be found.", vbOKOnly, "No column found!"
Exit Sub
End If

FormulaReferenceColumn = DebtCreditColumn - AMNTColumn

With Sheet1
    LastRow = .Cells(Rows.Count, DebtCreditColumn).End(xlUp).Row    'You can define whatever column works best for you
    Set TargetRange = .Range(.Cells(2, AMNTColumn), .Cells(LastRow, AMNTColumn))
End With

TargetRange.FormulaR1C1 = "=IF(RC[" & FormulaReferenceColumn & "]=""Debit"",RC[-1],IF(RC[" & FormulaReferenceColumn & "]=""Credit"",-RC[-1]))"


End Sub