#1
|
|||
|
|||
Selection.AutoFill Destination:=Range
Hello, I'm new to making Macros in Excel (and VB), I currently have to change data I get and made the following Macro. Of course, the problem I'm having is that the data length isnt always the same. Can someone help me out fix this range? I change the data in 2 different columns. TIA! Code:
CRL_FIX Macro ' Fix the SUP/WRK and Telephone data ' ' Columns("C:C").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.FormulaR1C1 = "=(RC[-2]&RC[-1])" Range("C1").Select Selection.AutoFill Destination:=Range("C1:C2201") Range("C1:C2201").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "CHGEWRK" Columns("AI:AI").Select ActiveCell.FormulaR1C1 = _ "=(LEFT(RC[-1],3)&""-""&MID(RC[-1],4,3)&""-""&RIGHT(RC[-1],4))" Range("AI1").Select Selection.AutoFill Destination:=Range("AI1:AI2201") Range("AI1:AI2201").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("AI1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "TELEPHONE" Columns("AH:AH").Select Selection.Delete Shift:=xlToLeft End Sub Last edited by Pecoflyer; 06-15-2019 at 04:50 AM. |
#2
|
||||
|
||||
Suggest you explain in detail what you want to happen instead of giving us code that does not do what you want. Additionally, suggest that you attach a before and after sheet in a workbook that corresponds to your explanation.
|
#3
|
||||
|
||||
I agree with Alansidman, so this is a guess:
Code:
Sub CRL_FIX() ' Fix the SUP/WRK and Telephone data ' lr = Application.Max(2, Cells(Rows.Count, "A").End(xlUp).Row) Columns("C:C").Insert With Range("C2:C" & lr) .FormulaR1C1 = "=(RC[-2]&RC[-1])" .Value = .Value End With Range("C1").Value = "CHGEWRK" With Range("AI2:AI" & lr) .FormulaR1C1 = "=(LEFT(RC[-1],3)&""-""&MID(RC[-1],4,3)&""-""&RIGHT(RC[-1],4))" .Value = .Value End With Range("AI1").Value = "TELEPHONE" Columns("AH:AH").Delete End Sub |
#4
|
||||
|
||||
@stillgate
Hi and welcome pleas wrap your code with code tags in the future ( edit your post - select the code - click the #button) Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autofill a field based on drop down list selection | lascough20 | Word | 2 | 09-21-2018 02:24 AM |
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug) | slaycock | Word VBA | 0 | 02-18-2017 07:00 AM |
Creating AutoFill form - AutoFill information depending on a selection. | RyanHarper | Word | 3 | 06-15-2015 01:01 PM |
Selection or Range | Tommes93 | Word VBA | 1 | 04-10-2014 02:50 AM |
Autofill a form which is contingent on a dropdown selection. | biffle0764 | Word | 2 | 05-09-2012 12:54 PM |