populating a string from cell values to check layout - fails when add line break

JugglerJAF

Active Member
Joined
Feb 17, 2002
Messages
297
Office Version
  1. 365
Platform
  1. Windows
I have a file that I want to use to generate a complex IF statement string that I paste into VBA to check the layout of a data file received from clients. My sheet has 3 columns:
  • A1: Col (contains column letters: A, B, C, D, etc.)
  • B1: Header (the expected values: alpha, bravo, charlie, delta etc.
  • C1: NewLine: True/False as to whether the code should start on a new line
Output value is:
If UCase([A1]) = "ALPHA" And UCase([B1]) = "BRAVO" And UCase([C1]) = "CHARLIE" And UCase([D1]) = "DELTA" Then

So far, so good, but because I'm potentially checking dozens of column headers, there comes a point where the VBA Editor can;t handle a single line of text over a certain length, so I want to split the string across several lines. In this example, I'd split after B to give:
If UCase([A1])="ALPHA" And UCase([B1])="BRAVO" And _
UCase([C1])="CHARLIE" And UCase([D1])="DELTA" Then

But the issue I'm having is that when I enter in a line break after the "And _", and paste it to the VBA Editor, the whole string is enclosed in quotes, and each entry checked for is surrounded by 2 double quotes:
"If UCase([A1])=""ALPHA"" And UCase([B1])=""BRAVO"" And _
UCase([C1])=""CHARLIE"" And UCase([D1])=""DELTA"" Then"

Is there a way to code it so that a line break is entered without treating the entire things as a quote enclosed string?

Current code is:
VBA Code:
Sub generate_check_string()
    check_str = "If "
    Range("B2").Select
    If ActiveCell.Offset(1, 0).Value = "" Then
        check_str = check_str & "UCase([" & ActiveCell.Offset(0, -1).Value & 1 & "])="""
        check_str = check_str & UCase(ActiveCell.Value) & """ Then"
    Else
        Range(ActiveCell, ActiveCell.End(xlDown)).Select
        For Each c In Selection
            check_str = check_str & "UCase([" & c.Offset(0, -1).Value & 1 & "])="""
            check_str = check_str & UCase(c.Value) & """"
            If c.Offset(1, 0).Value = "" Then
                check_str = check_str & " Then"
            Else
                If c.Offset(0, 1).Value = True Then
                    check_str = check_str & " And " 'single line: works
                    '..or...'
                    'check_str = check_str & " And _" & vbCr 'split line: fails
                Else
                    check_str = check_str & " And "
                End If
            End If
        Next c
    End If
    '
    Range("G2").Select
    ActiveCell.Value = check_str
    Range("G2").WrapText = True
End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I've sort of resolved the issue. The problem was that the generated string was being populated to a cell on the sheet, then manually copied and pasted to the VBA Editor.

I've got around this by changing the code to put the generated value to clipboard, and I can then paste directly from clipboard to VBA Editor.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top