Macro Question

[adunlap13]

Hi,

I have data that comes to me the same way everyday but it varies in length. I recorded a macro for 80 sets of data and then put in 50 sets the next day and it showed N/A's or numbers for the last 30 spaces since there was no data in them. How do I tweak the code to use it on an length of data and that's it? I'm guessing it has to do with an IF Then statement but I'm not sure.

Thanks in advance for your help

 

[hiker95]

adunlap13,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

I recorded a macro

3. Can we see your macro code?


When posting VBA code, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]

 

[My Aswer Is This]

Your new here and Welcome to Mr. Excel.
Anytime you have questions about your code you should post it here.
Click on the # icon above and paste in your code

 

[adunlap13]

Sorry about that. I am new to the forum. I am running windows with MS Excel 2010.

I still need to clean it up but please see code below:

Sub penaltypmt()
'
' penaltypmt Macro
'
' Keyboard Shortcut: Ctrl+z
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(32, 9), Array(45, 1), Array(53, 9), Array(59, 1), _
Array(70, 9)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("D10").Select
Sheets("MSP Codes").Select
Range("I5").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R10C1"
Range("I6").Select
Sheets("Sheet2").Select
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:D").Select
Range("D1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R2C9"
Range("C15").Select
Selection.AutoFill Destination:=Range("C15:C102")
Range("C15:C102").Select
Range("D15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R3C9"
Range("D15").Select
Selection.AutoFill Destination:=Range("D15:D102")
Range("D15:D102").Select
Range("E15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R4C9"
Range("E15").Select
Selection.AutoFill Destination:=Range("E15:E102")
Range("E15:E102").Select
Range("F15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R6C9"
Range("F15").Select
Selection.AutoFill Destination:=Range("F15:F102")
Range("F15:F102").Select
ActiveWindow.SmallScroll Down:=6
Range("H15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/'MSP Codes'!R7C9"
Range("H15").Select
Selection.AutoFill Destination:=Range("H15:H102")
Range("H15:H102").Select
Range("I15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*'MSP Codes'!R8C9"
Range("I15").Select
Selection.AutoFill Destination:=Range("I15:I102")
Range("I15:I102").Select
Range("J15").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'MSP Codes'!C[-9]:C[-8],2,FALSE)"
Range("J15").Select
Selection.AutoFill Destination:=Range("J15:J102")
Range("J15:J102").Select
Columns("J:J").EntireColumn.AutoFit
Range("K15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R5C9"
Range("K15").Select
Selection.AutoFill Destination:=Range("K15:K102")
Range("K15:K102").Select
Columns("K:K").EntireColumn.AutoFit
End Sub

 

[adunlap13]

Hi,

I am new to Mr. Excel.

I replied to the previous post under my question so I'm sorry if you are seeing the code twice.

Sub penaltypmt()
'
' penaltypmt Macro
'
' Keyboard Shortcut: Ctrl+z
'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(32, 9), Array(45, 1), Array(53, 9), Array(59, 1), _
Array(70, 9)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("D10").Select
Sheets("MSP Codes").Select
Range("I5").Select
ActiveCell.FormulaR1C1 = "=Sheet2!R10C1"
Range("I6").Select
Sheets("Sheet2").Select
Columns("C:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("C:D").Select
Range("D1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("F:F").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("B:B").Select
Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("C15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R2C9"
Range("C15").Select
Selection.AutoFill Destination:=Range("C15:C102")
Range("C15:C102").Select
Range("D15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R3C9"
Range("D15").Select
Selection.AutoFill Destination:=Range("D15:D102")
Range("D15:D102").Select
Range("E15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R4C9"
Range("E15").Select
Selection.AutoFill Destination:=Range("E15:E102")
Range("E15:E102").Select
Range("F15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R6C9"
Range("F15").Select
Selection.AutoFill Destination:=Range("F15:F102")
Range("F15:F102").Select
ActiveWindow.SmallScroll Down:=6
Range("H15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/'MSP Codes'!R7C9"
Range("H15").Select
Selection.AutoFill Destination:=Range("H15:H102")
Range("H15:H102").Select
Range("I15").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*'MSP Codes'!R8C9"
Range("I15").Select
Selection.AutoFill Destination:=Range("I15:I102")
Range("I15:I102").Select
Range("J15").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-8],'MSP Codes'!C[-9]:C[-8],2,FALSE)"
Range("J15").Select
Selection.AutoFill Destination:=Range("J15:J102")
Range("J15:J102").Select
Columns("J:J").EntireColumn.AutoFit
Range("K15").Select
ActiveCell.FormulaR1C1 = "='MSP Codes'!R5C9"
Range("K15").Select
Selection.AutoFill Destination:=Range("K15:K102")
Range("K15:K102").Select
Columns("K:K").EntireColumn.AutoFit
End Sub