Posts by mrmmickle1

mrmmickle1 · Aug 24th 2017

Re: Kill File

Try:

Code

Kill MyPath & MyFile

or make a new variable that combines the two...

mrmmickle1 · Aug 24th 2017

Re: Listbox items with date display numbers instead of date format in a textbox

What's the format of values in your worksheet? What is the format of the values appear in the textboxes of your userform?

You should be able to use the Format() Function to format the range value however you like... Here's an Example:

findvalue.Offset(0,6) = Format(Me.txtStu7, "MM-DD-YY")

mrmmickle1 · Aug 17th 2017

Re: popupcalender: save old date before choose a new date

The calendar you're using is not usually supported in other user's versions of Excel. I would recommend using a different one. Not quite sure what you're trying to do... Most of the time pictures aren't very helpful and you're explanation is difficult to understand.

mrmmickle1 · Aug 15th 2017

Re: Filter not working for second criteria

Rory answered your question in this post a while back... you need to use a helper column:

http://www.ozgrid.com/forum/showthread.php?t=153014

Quote

Use an additional column with a formula that tries to match the value to your list of values, then filter on that column where there is no match. You cannot use an array in an autofilter where you want to exclude the array values (you can only include them).

mrmmickle1 · Aug 15th 2017

Re: Separate data from each owner into separate sheet?

Xani,

Here is an example where I filter data based on Column A (Column 1) based on certain names and then move the data to the respective worksheet. You can probably use this as a base and modify it as needed....

Code

'=========================================================================='
'=========================================================================='
'Module Type: Code Module                                                  '
'Purpose    : Filter data and paste to corresponding worksheets            '
'=========================================================================='
'Author     : Matt Mickle                                                  '                                  '
'Date       : 07.20.2017                                                  '
'=========================================================================='




Sub FilterAndPlaceData()




    'Declarations
    Dim shtSource        As Worksheet
    Dim shtDestination   As Worksheet
    Dim arrShts()        As Variant
    Dim shtLp            As Integer
    Dim filterCol        As Integer
    Dim lcol             As Integer
    Dim lrow             As Integer
    
    Set shtSource = Sheets("All AEs") 'Define source worksheet
    
    arrShts = Array("Ditas", "Eric", "John", "Leslie", "Neil", "Shera", "Tim") 'Define destination worksheet names
    filterCol = 1 'Define Column to filter on....
    
    With shtSource
    shtSource.Activate 'Activate Sheet so filter will work properly
    lcol = .Cells(1, Columns.Count).End(xlToLeft).Column 'Define last column of source worksheet
    lrow = .Cells(Rows.Count, "A").End(xlUp).Row 'Define last row of source worksheet
    
        'Loop through names to filter on...
        For shtLp = 0 To UBound(arrShts) - 1
        
             Set shtDestination = Sheets(arrShts(shtLp))
             
                 With .Range(Cells(1, 1), Cells(lrow, lcol))
                     .AutoFilter filterCol, "=" & arrShts(shtLp) & "*" 'Filter on X Employee (Notice the asterisk as a wildcard)
                     .Offset(1).SpecialCells(xlCellTypeVisible).Copy shtDestination.Range("A2") 'Copy All Employee Data to Appropriate Worksheet
                     .AutoFilter 'UnFilter
                 End With
                         
         Next shtLp 'Go to Next Employee Name / Worksheet
     
    End With








End Sub

Display More

mrmmickle1 · Feb 8th 2017

Re: Replace text or leave it alone pls.

With this line of code:

Code

If Not c.Value = "" Then curVal = c.Value

You are essentially saying: If a cell in rngR has a value in it then replace whatever values in Rng have curVal in it with c.Offset(0,2).Value

Is this what you want to do? If not what do you expect it to do? Can you give a before and after example or post a sample workbook with non-sensitive information?

mrmmickle1 · Feb 3rd 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

You're welcome. Glad you got it working.

mrmmickle1 · Jan 30th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Sure just use this amendment:

Code

Sub Macro1() 
    
    Dim Lrow As Long




    LRow = Cells(Rows.Count, "D").End(xlUp).Row 'Define Last Row




    Range("D25:EE" & Lrow).FormatConditions.Add Type:=xlExpression, Formula1:="=OR(D25>D$5)" 
    Range("D25:EE" & Lrow).FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Range("D25:EE" & Lrow).FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 255 
        .TintAndShade = 0 
    End With 
     
     
    Range("D25:EE" & Lrow).FormatConditions.Add Type:=xlExpression, Formula1:="=OR(D25<D$6)" 
    Range("D25:EE" & Lrow).FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
    With Range("D25:EE" & Lrow).FormatConditions(1).Interior 
        .PatternColorIndex = xlAutomatic 
        .Color = 255 
        .TintAndShade = 0 
    End With 




End Sub

Display More

mrmmickle1 · Jan 25th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Change this line to :

Code

With ActiveSheet

Code

With Sheets("YOUR_SHEET_THAT_CONTAINS_DATA'S_NAME_HERE")

mrmmickle1 · Jan 25th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

Did you leave the Upper and Lower Tolerances on Row 5 and 6? Or did they move too? What about the Column the tolerances start on? If so you need to update these lines as well:

Code

'Remember the Arguments for .Cells go like this .Cells (Row, Column)
       LCol = .Cells(5, Columns.Count).End(xlToLeft).Column '<--------------        
         'Column 4 means Column D 
        For ColLp = 4 To LCol 
             
            UpTol = .Cells(5, ColLp) 'Define Upper Tolerance '<--------------
            LowTol = .Cells(6, ColLp) 'Define Lower Tolerance '<-------------


                 For RowLp = 25 To LRow

Where did you paste the code?

mrmmickle1 · Jan 24th 2017

Re: Excel VBA Userform Keeping a running total of textboxes formatted to 2 decimal pl

Use something like this:

Code

Hour15.Value = Format(CDbl(Hour1.Value) + CDbl(Hour2.Value), "#,##0.00")

Your values are being read as text. You should consider converting them to a decimal using the CDBL() ,CSNG() or VAL() Functions.

Also, you should get rid of all of the Exit and Change Events....you probably don't realize it, but when you write code like this you actually trigger a ton of events:

Code

If Hour1.Value = "" Then Hour1.Value = 0
    If Hour2.Value = "" Then Hour2.Value = 0
    If Hour3.Value = "" Then Hour3.Value = 0
    If Hour4.Value = "" Then Hour4.Value = 0
    If Hour5.Value = "" Then Hour5.Value = 0
    If Hour6.Value = "" Then Hour6.Value = 0
    If Hour7.Value = "" Then Hour7.Value = 0
    If Hour8.Value = "" Then Hour8.Value = 0
    If Hour9.Value = "" Then Hour9.Value = 0
    If Hour10.Value = "" Then Hour10.Value = 0
    If Hour11.Value = "" Then Hour11.Value = 0
    If Hour12.Value = "" Then Hour12.Value = 0
    If Hour13.Value = "" Then Hour13.Value = 0
    If Hour14.Value = "" Then Hour14.Value = 0

Display More

Just have one button that's called calculate and have the user press it manually. This will save a ton of unneeded processing time....or I would suggest looking into using:

Code

Application.EnableEvents = False
'Your Code
Application.EnableEvents =True

mrmmickle1 · Jan 24th 2017

Re: How to Copy a group of cells from an offset relevant to a Search String

Use ReSize:

Code

Sub Test()




     '=========================================================================================='
     '=========================================================================================='
     'Module Type: Code Module                                                                  '
     'Purpose    : Find X Cell based on criteria then copy 13 cells to right using .Resize      '
     '=========================================================================================='
     'Author     : Matt Mickle                                                                  '
     'LinkedIn   : https://www.linkedin.com/in/matthew-mickle-2101305b                          '
     'Date       : 01.24.2017                                                                   '
     'Forum      : OzGrid                                                                       '
     'URL        : http://www.ozgrid.com/forum/showthread.php?t=202568&p=784267#post784267      '
     '=========================================================================================='




    Dim SrchRng As Range, cel As Range




    Set SrchRng = Range("AO:AO") 'May Consider changing this range it is to large.... Maybe Something Smaller Range("AO2:AO500") ???
    For Each cel In SrchRng
        If cel.Value = Worksheets("Main").Range("A2") Then
            cel.Offset(0, 1).Resize(1, 13).Copy 'From your offset cell get 13 total
                                                'columns (this includes the offset cell)
        End If
    Next cel
    
End Sub

Display More

mrmmickle1 · Jan 24th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

You're Welcome. Keep me posted if you need adjustments.

mrmmickle1 · Jan 24th 2017

Re: Highlight cells in columns in dynamic range that meet criteria with VBA

If your data appears like this:
[TABLE="width: 448"]

[tr]

[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]25[/TD]
[TD="width: 64, align: right"]30[/TD]
[TD="width: 64, align: right"]35[/TD]
[TD="width: 64, align: right"]40[/TD]

[/tr]

[tr]

[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]

[/tr]

[tr]

[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]

[/tr]

[tr]

[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[/tr]

[tr]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[/tr]

[tr]

[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[/TABLE]

Then use code like this:

Code

Sub Test()


'=========================================================================='
'=========================================================================='
'Module Type: Code Module                                                  '
'Purpose    : Cycle through values and highlight red if out of tolerance   '
'=========================================================================='
'Author     : Matt Mickle                                                  '
'LinkedIn   : https://www.linkedin.com/in/matthew-mickle-2101305b          '
'Date       : 01.24.2017                                                   '
'Forum      : OzGrid                                                       '
'URL        : http://www.ozgrid.com/forum/showthread.php?t=202563          '
'=========================================================================='
    
    'Declarations
    Dim LCol    As Integer
    Dim LRow    As Long
    Dim RowLp   As Long
    Dim ColLp   As Long
    Dim UpTol   As Long
    Dim LowTol  As Long
    
    With ActiveSheet
    
        LRow = .Cells(Rows.Count, "D").End(xlUp).Row 'Define Last Row
        LCol = .Cells(5, Columns.Count).End(xlToLeft).Column 'Define Last Column
        
        'Loop through Columns and Rows one at a time
        For ColLp = 4 To LCol
            
            UpTol = .Cells(5, ColLp) 'Define Upper Tolerance
            LowTol = .Cells(6, ColLp) 'Define Lower Tolerance
            
            For RowLp = 10 To LRow
                
                'Test for values out of tolerance
                If .Cells(RowLp, ColLp) > UpTol Or _
                   .Cells(RowLp, ColLp) < LowTol Then
                   
                    .Cells(RowLp, ColLp).Interior.Color = vbRed 'Color Cell Red if it is out of tolerance
            
                End If
                
            Next RowLp
        Next ColLp
    
    End With
    
End Sub

Display More

This will result in highlighting the following cells Red:

[TABLE="width: 448"]

[tr]

[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]15[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]25[/TD]
[TD="width: 64, align: right"]30[/TD]
[TD="width: 64, align: right"]35[/TD]
[TD="width: 64, align: right"]40[/TD]

[/tr]

[tr]

[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="class: xl65, align: right"]16[/TD]
[TD="class: xl65, align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]17[/TD]
[TD="class: xl65, align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]16[/TD]
[TD="class: xl65, align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]17[/TD]
[TD="class: xl65, align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]4.9[/TD]
[TD="class: xl65, align: right"]4.9[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]3[/TD]

[/tr]

[tr]

[TD="class: xl65, align: right"]17[/TD]
[TD="class: xl65, align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]

[/tr]

[tr]

[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]
[TD="align: right"]6.5[/TD]

[/tr]

[/TABLE]

mrmmickle1 · Dec 6th 2016

Re: Button to add contents of A1 to B1, then return A1 to zero

El Gallo,

Try something like this:

Code

Sub Test()
    Range("B1").Value = Range("B1") + Range("A1")
    Range("A1") = "=SUM(A2)"
End Sub

mrmmickle1 · Sep 30th 2016

Re: Concatenate the values, based on the conditions matched

Here's a little bit simpler solution:

Code

Sub Test()
    Dim LRow As Integer, intLp  As Integer
    LRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    For intLp = LRow To 1 Step -1
        If intLp > 1 Then
            If Cells(intLp, 2) = Cells(intLp - 1, 2) Then
                Cells(intLp - 1, 1) = Cells(intLp - 1, 1) & ", " & Cells(intLp, 1)
                Cells(intLp, 1).EntireRow.Delete
            End If
        End If
    Next intLp
End Sub

Display More

mrmmickle1 · Sep 29th 2016

Re: Excel VBA UserForm to Display Cell Location

You're welcome. Glad you got it working.

mrmmickle1 · Sep 29th 2016

Re: Excel VBA UserForm to Display Cell Location

Just lock the textbox so it's not editable:

Code

Private Sub UserForm_Initialize()
    Dim cTextBox As Control
    Dim r As Long, c As Range
    Dim Loc As Characters
    Set cTextBox = Controls.Add("Forms.TextBox.1")
    With cTextBox
        .Height = 20
        .Width = 200
        .Left = 15
        cTextBox = CStr(ThisWorkbook.Sheets("DATA INPUT SHEET").Range(ActiveCell.Address).Address(False, False))
        .Locked = True
    End With
End Sub

Display More

mrmmickle1 · Sep 28th 2016

Re: Using a string variable as a formula

If you values are A1:A1000 and B1:B1000 and you want to concatenate using a formula in C1:C1000 then I think this is what you want.... no iteration necessary:

Code

Range("C1:C1000") = "=""T""&""QQQQ_""&Right(A1, 3)&" & """_""&Right(B1, 3)"

mrmmickle1 · Sep 27th 2016

Re: Using a string variable as a formula

If you have these values in Cell A1 (XXX) and A2 (YYY) and you want A3 to equal your string then you can use the following.....

Code

Sub Test()
    Range("A3") = "T" & "QQQQ" & "_" & Right(Range("A1"), 3) & "_" & Right(Range("A2"), 3)
End Sub

A3 now has this value:

[TABLE="width: 114"]

[tr]

[TD="class: xl63, width: 114"]TQQQQ_XXX_YYY[/TD]

[/tr]

[/TABLE]