Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-14-2019, 01:50 PM
Stillgate Stillgate is offline Selection.AutoFill Destination:=Range Windows 7 32bit Selection.AutoFill Destination:=Range Office 2010 32bit
Novice
Selection.AutoFill Destination:=Range
 
Join Date: Aug 2018
Posts: 3
Stillgate is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 06-14-2019, 03:53 PM
Alansidman's Avatar
Alansidman Alansidman is offline Selection.AutoFill Destination:=Range Windows 10 Selection.AutoFill Destination:=Range Office 2019
Advanced Beginner
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 47
Alansidman is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 06-15-2019, 04:20 AM
p45cal p45cal is online now Selection.AutoFill Destination:=Range Windows 10 Selection.AutoFill Destination:=Range Office 2016
Expert
 
Join Date: Apr 2014
Posts: 427
p45cal is just really nicep45cal is just really nicep45cal is just really nicep45cal is just really nice
Default

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
Reply With Quote
  #4  
Old 06-15-2019, 04:49 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Selection.AutoFill Destination:=Range Windows 7 64bit Selection.AutoFill Destination:=Range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,434
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

@stillgate
Hi and welcome
pleas wrap your code with code tags in the future ( edit your post - select the code - click the #button)
Thanks
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Selection.AutoFill Destination:=Range 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
Selection.AutoFill Destination:=Range Autofill a form which is contingent on a dropdown selection. biffle0764 Word 2 05-09-2012 12:54 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:09 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft