0

I have two columns with values (blank or characters), and I need to combine the information in those two columns into the column I'm working in. If there's only information in one of those columns, I want that information, but if there's information in both I need it to appear as "X/Y"

Examples for a given row:

Case 1: TaxableBox contains "14" and VATBox contains "1"; return "14/1"

Case 2: TaxableBox contains "" and VATBox contains "1"; return "1"

Case 3: TaxableBox contains "14" and VATBox contains ""; return "14"

Case 4: TaxableBox contains "" and VATBox contains ""; return ""

The code below worked before, but now it isn't working. I suspect It might have something to do with ActiveCell, but I'm not sure why it has a problem now when it didn't before. Any help would be appreciated.

Dim taxableBox As Range
Dim vatBox As Range
Dim i As Integer
Set taxableBox = Worksheets("VAT return layout").Range("2:2").Find("Box number, Taxable Amount")
Set vatBox = Worksheets("VAT return layout").Range("2:2").Find("Box number, VAT Amount")

Worksheets("Expected VAT return").Range("B4").Select
For i = 1 To LastRow
    If [taxableBox].Offset(i, 0) <> "" And [vatBox].Offset(i, 0) <> "" Then
        ActiveCell.Value = [taxableBox].Offset(i, 0) & "/" & [vatBox].Offset(i, 0)
    ElseIf [taxableBox].Offset(i, 0) <> "" And [vatBox].Offset(i, 0) = "" Then
        ActiveCell.Value = [taxableBox].Offset(i, 0)
    ElseIf [taxableBox].Offset(i, 0) = "" And [vatBox].Offset(i, 0) <> "" Then
        ActiveCell.Value = [vatBox].Offset(i, 0)
    ElseIf [taxableBox].Offset(i, 0) = "" And [vatBox].Offset(i, 0) = "" Then
        ActiveCell.Value = ""
    End If
    ActiveCell.Offset(1, 0).Select
Next i
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Beeks
  • 1
  • 5
    most likely the find is returning nothing because it is not finding a match. – Scott Craner Apr 09 '19 at 14:35
  • 2
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • And row counting variables need to be of type `Long` because Excel has more rows than `Integer` can handle: `Dim i As Long`. – Pᴇʜ Apr 09 '19 at 14:36
  • 1
    Wow, a very quick and very simple answer. Thanks so much, you were right. I went through all the work to make an account for that haha! – Beeks Apr 09 '19 at 14:37
  • I'll look into it @Pᴇʜ. I'm -very- new to VBA so I could almost definitely stand to learn more. – Beeks Apr 09 '19 at 14:38

0 Answers0