Hold the value of a variable until something else happens

L

line_lina

New Member
Joined
Mar 27, 2011
Messages
6
hello!

I'm basically trying to write a code to do the concatenate at column C:

<TABLE style="WIDTH: 326pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=433 border=0><COLGROUP><COL style="WIDTH: 16pt; mso-width-source: userset; mso-width-alt: 768" width=21><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><COL style="WIDTH: 154pt; mso-width-source: userset; mso-width-alt: 7497" width=205><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 16pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=21 height=20>
1​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=66>
Column A​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 106pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=141>
Column B​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 154pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=205>
Column C​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
2​
</TD><TD class=xl301 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
2704​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
ASSY (1704)​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
3​
</TD><TD class=xl297 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
C3​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>
0​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
=CONCATENATE($B$2,"CM",A3)​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
4​
</TD><TD class=xl298 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
D2​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>
0​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
=CONCATENATE($B$2,"CM",A4)​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
5​
</TD><TD class=xl298 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
HH​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>
0.8​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
=CONCATENATE($B$2,"CM",A5)​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
6​
</TD><TD class=xl298 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
HI​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>
0.2​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
=CONCATENATE($B$2,"CM",A6)​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
7​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
8​
</TD><TD class=xl301 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
2705​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
ASSY (1705)​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
9​
</TD><TD class=xl298 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
D1​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>
0​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
=CONCATENATE($B$8,"CM",A9)​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
10​
</TD><TD class=xl298 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
HH​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>
0.65​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
=CONCATENATE($B$8,"CM",A10)​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>
11​
</TD><TD class=xl298 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
HI​
</TD><TD class=xl300 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>
0.35​
</TD><TD class=xl299 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">
=CONCATENATE($B$8,"CM",A11)​
</TD></TR></TBODY></TABLE>


The problem is that I don't know how to hold the variable (like $C$8). :(
The value that I need to hold is always after a blank cell so it is easy to identify it.

The code so far is:

Code: 
Sub Connect()
Dim m As Integer
Dim n As Integer
Dim Account As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
            
    
     ThisWorkbook.Sheets("Sheet2").Columns("C:C").Select
     Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
 
For m = 3 To 10
For n = 3 To 10
        
    
    If ThisWorkbook.Sheets("Sheet2").Range("A" & n) = " " And _
    ThisWorkbook.Sheets("Sheet2").Range("B" & (n + 1)) <> " " Then
        m = n
        
        Account = ThisWorkbook.Sheets("Sheet2").Range("B" & m)
        
        ThisWorkbook.Sheets("Sheet2").Range("C" & n) = _
        Account & "CM" & ThisWorkbook.Sheets("Sheet2").Range("A" & n)
        
    ElseIf ThisWorkbook.Sheets("Sheet2").Range("A" & n) <> " " And _
    ThisWorkbook.Sheets("Sheet2").Range("B" & (n + 1)) <> " " Then
        'concatenate
        m = n - 1
    
        Account = ThisWorkbook.Sheets("Sheet2").Range("B" & m)
        
        ThisWorkbook.Sheets("Sheet2").Range("C" & n) = _
        Account & "CM" & ThisWorkbook.Sheets("Sheet2").Range("A" & n)
        
     ElseIf ThisWorkbook.Sheets("Sheet2").Range("A" & n) <> " " And _
    ThisWorkbook.Sheets("Sheet2").Range("B" & (n + 1)) = " " Then
        'concatenate
        m = n - 2
    
        Account = ThisWorkbook.Sheets("Sheet2").Range("B" & m)
        
        ThisWorkbook.Sheets("Sheet2").Range("C" & n) = _
        Account & "CM" & ThisWorkbook.Sheets("Sheet2").Range("A" & n)
        
      Else
        n = n + 1
      
      End If
      
Next n
Next m
End Sub

Any suggestions??? :confused:
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
line_lina,


Sample data before the macro in worksheet Sheet2:


Excel Workbook
ABC
1
22704ASSY (1704)
3C30
4D20
5HH0.8
6HI0.2
7
82705ASSY (1705)
9D10
10HH0.65
11HI0.35
12
Sheet2





After the macro in worksheet Sheet2:


Excel Workbook
ABC
1
22704ASSY (1704)
3C30ASSY (1704)CMC3
4D20ASSY (1704)CMD2
5HH0.8ASSY (1704)CMHH
6HI0.2ASSY (1704)CMHI
7
82705ASSY (1705)
9D10ASSY (1705)CMD1
10HH0.65ASSY (1705)CMHH
11HI0.35ASSY (1705)CMHI
12
Sheet2





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Option Explicit
Sub ConcatData()
' hiker95, 04/16/2011
' http://www.mrexcel.com/forum/showthread.php?t=544068
Dim BArea As Range, SR As Long, ER As Long
Application.ScreenUpdating = False
Worksheets("Sheet2").Activate
For Each BArea In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With BArea
    SR = .Row
    ER = SR + .Rows.Count - 1
    Range("C" & SR + 1).Formula = "=CONCATENATE($B$" & SR & ",""CM"",A" & SR + 1 & ")"
    Range("C" & SR + 1).AutoFill Destination:=Range("C" & SR + 1 & ":C" & ER)
  End With
Next BArea
Application.ScreenUpdating = True
End Sub


Then run the ConcatData macro.
 
Last edited:
Upvote 0
:) :) :)

WAO!!! That works super nice when I try it my back up file, but when I use it at my complete spreadsheet I'm getting the following error: AutoFill method or Range class failed

I think that it is my fault since I never mentioned that I could an account with all the weight (=1):
2806 Screening<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
HI 1<o:p></o:p>
<o:p></o:p>
2702 supp<o:p></o:p>
C3 0.75<o:p></o:p>
HH 0.145<o:p></o:p>
HI 0.025<o:p></o:p>
N2 0.08


Do you think that this makes a difference?

I know that it is too much to ask but can you let me know how to fix this?

Thanksssss :biggrin:
 
Upvote 0
line_lina,

If the macro does not work correctly becuase your actual dataset is different than what you have shown, then I need to see actual screenshots of your raw data, and what the outcome should be.

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net
 
Upvote 0
line_lina said:
I think that it is my fault since I never mentioned that I could an account with all the weight (=1):
2806 Screening
HI 1

2702 supp
C3 0.75
HH 0.145
HI 0.025
N2 0.08
Click to expand...
Try this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Concat_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> B_Area <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> FormulaBase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=#&""CM""&^"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> B_Area <SPAN style="color:#00007F">In</SPAN> .Range("B2", .Range("B" & .Rows.Count).End(xlUp)) _<br>                .SpecialCells(xlCellTypeConstants).Areas<br>            <SPAN style="color:#00007F">With</SPAN> B_Area<br>                f = Replace(FormulaBase, "#", .Cells(1, 1).Address(1, 1))<br>                f = Replace(f, "^", .Cells(1, 1).Offset(1, -1).Address(0, 0))<br>                <SPAN style="color:#00007F">With</SPAN> .Offset(1, 1).Resize(.Rows.Count - 1)<br>                    .Formula = f<br>                    .Value = .Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> B_Area<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


Sample results:

Excel Workbook
ABC
1
22704ASSY (1704)
3C30ASSY (1704)CMC3
4D20ASSY (1704)CMD2
5HH0.8ASSY (1704)CMHH
6HI0.2ASSY (1704)CMHI
7
82705ASSY (1705)
9D10ASSY (1705)CMD1
10HH0.65ASSY (1705)CMHH
11HI0.35ASSY (1705)CMHI
12
132806Screening
14HI1ScreeningCMHI
15
162702supp
17C30.75suppCMC3
18HH0.145suppCMHH
19HI0.025suppCMHI
20N20.08suppCMN2
Sheet2
 
Upvote 0
line_lina,


Sample data before the macro:


Excel Workbook
ABC
1
22704ASSY (1704)
3C30
4D20
5HH0.8
6HI0.2
7
82705ASSY (1705)
9D10
10HH0.65
11HI0.35
12
132806Screening
14HI1
15
162702supp
17C30.75
18HH0.145
19HI0.025
20N20.08
21
Sheet2





After the updated macro:


Excel Workbook
ABC
1
22704ASSY (1704)
3C30ASSY (1704)CMC3
4D20ASSY (1704)CMD2
5HH0.8ASSY (1704)CMHH
6HI0.2ASSY (1704)CMHI
7
82705ASSY (1705)
9D10ASSY (1705)CMD1
10HH0.65ASSY (1705)CMHH
11HI0.35ASSY (1705)CMHI
12
132806Screening
14HI1ScreeningCMHI
15
162702supp
17C30.75suppCMC3
18HH0.145suppCMHH
19HI0.025suppCMHI
20N20.08suppCMN2
21
Sheet2





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Option Explicit
Sub ConcatDataV2()
' hiker95, 04/17/2011
' http://www.mrexcel.com/forum/showthread.php?t=544068
Dim BArea As Range, SR As Long, ER As Long
Application.ScreenUpdating = False
Worksheets("Sheet2").Activate
For Each BArea In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With BArea
    SR = .Row
    ER = SR + .Rows.Count - 1
    If .Rows.Count = 2 Then
      Range("C" & SR + 1).Formula = "=CONCATENATE($B$" & SR & ",""CM"",A" & SR + 1 & ")"
    Else
      Range("C" & SR + 1).Formula = "=CONCATENATE($B$" & SR & ",""CM"",A" & SR + 1 & ")"
      Range("C" & SR + 1).AutoFill Destination:=Range("C" & SR + 1 & ":C" & ER)
    End If
  End With
Next BArea
Application.ScreenUpdating = True
End Sub


Then run the ConcatDataV2 macro.
 
Upvote 0
Perfect!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! :) :) :)

Sorry to mention that before I didn't notice it since this will work with more than 15 different types of files! :biggrin:

Thank you so much and have a wonderful day!!!​
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
508
esben
E

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

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