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: 4
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
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 79
Alansidman will become famous soon enoughAlansidman will become famous soon enough
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's Avatar
p45cal p45cal is offline Selection.AutoFill Destination:=Range Windows 10 Selection.AutoFill Destination:=Range Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
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 online now Selection.AutoFill Destination:=Range Windows 7 64bit Selection.AutoFill Destination:=Range Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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
__________________
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
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

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


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