Macro Help

[Tennisguuy]

I'm not good with writing code for macros so I always use the record macro to set up a macro and in most cases that's enough. However, I have a situation where I would like to create macro to alleviate doing the same task every time.

I have a template that I use to organize data on every account I underwrite. It helps me analyze the data. In the template I have a section with the data below.

Loc. T.I.V Coverage
1.1 100,000 b
200,000 c
300,000 Stock
1.2 200,000 b
400,000 c
600,000 Income

The amount of data various based on the size of account. From this data I use excel to organize it by it by location by T.I.V by coverage

What I do is copy the data you see above over to another column and I will use excel to fill in the blank spaces for the number so I can get a total. Once I copy the data into another section I highlight the loc column press F5 click on the blank radio button then hit the equal sign then ctrl enter and excel will fill in the blank spaces. For example it would be a 1.1 next to the 200,000 and 1.1 next to the 300,000.

I created a macro to perform this task but in order to create the macro and since I have no idea how large my data set will be each time I had it copy up to 600 rows. However when it use excel to fill in the blank spaces and if I don't have exactly 600 rows of data it will take the last number and paste it all the way down to line 600. Since there is no data in the other cells it doesn't affect my calculation it just doesn't look at nice to have a location number without a value next to it.

Therefore what I wanted to know if the macro could be edited to only fill in a number in a blank spot where there is data next to it. The problem is even if it could be done I wouldn't know how to edit the code. I've copied the code from the macro recorder below.



Sub Macro4()
'
' Macro4 Macro
' Mascro to copy location numbers to column t for processing
'
' Keyboard Shortcut: Ctrl+p
'
Range("N2:N601").Select
Selection.Copy
ActiveWindow.ScrollRow = 578
ActiveWindow.ScrollRow = 576
ActiveWindow.ScrollRow = 571
ActiveWindow.ScrollRow = 565
ActiveWindow.ScrollRow = 556
ActiveWindow.ScrollRow = 546
ActiveWindow.ScrollRow = 525
ActiveWindow.ScrollRow = 512
ActiveWindow.ScrollRow = 495
ActiveWindow.ScrollRow = 478
ActiveWindow.ScrollRow = 446
ActiveWindow.ScrollRow = 429
ActiveWindow.ScrollRow = 415
ActiveWindow.ScrollRow = 382
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 243
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 49
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 2
ActiveWindow.SmallScroll ToRight:=27
Range("AK2").Select
ActiveSheet.Paste
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=R[-1]C"
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
End Sub

 

[par60056]

First, macros almost never need window scrolling. It is slow and doesn't do anything useful except if you need to scroll to the top when done. So remove scrolling from your recorded macros.

Second, the recorder likes to select things and then work on selection. This is almost never needed. Try to edit out any selections and see if it still works.

Third, always use Long type for row variables even it you know it is only a couple hundred rows. Good habits start early.

Forth, use the [C o d e ] and [/c o d e] tags when posting code.

I edited your code down. This is what I came up with:

Sub Macro4()
'
' Macro4 Macro
' Mascro to copy location numbers to column t for processing
'
' Keyboard Shortcut: Ctrl+p

'  Find the last row
' There are 3 main ways to find the last row, each has issues.
' I prefer using UsedRange.  While it can get messed up, it is usually right

Dim lastRow As Long

lastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

'I dislike selecting things if it isn't needed.
Range("N2:N" & lastRow).Copy Range("AK2")
Range("AK2:AK" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

 

[Tennisguuy]

Thanks for the reply. I really appreciate it. I'm a novice when it comes to coding a macro which is why I use the macro recorder. I have no experience in writing code. While I understand what you say above I'm not experience enough in macro coding to make the necessary suggestion you listed above. I replaced the code with your coding above and while the macro still works, the problem I was trying to solve is still there and I'm not sure if it can be corrected because of how I'm using the F5 function to fill in the blank spots

In the original example I gave above

location T.I.V Coverage
1.1 100,000 b
200,000 c
300,000 stock
2.1 200,000 b
400,000 c
600,000 Income

I would use the F5 function to have excel fill in the blank for example the above would become

Location T.IV Coverage
1.1 100,000 b
1.1 200,000 c
1.1 300,000 stock
2.1 200,000 b
2.1 400,000 c
2.1 600,000 Income

The problem I'm having is that since I set up the range to go to line 600 just because I never know how big my data set will be when the macro gets to the last line which in this case is 2.1 it copies 2.1 all the way down to line 600 which is what I was trying to avoid. I was curious if there was a way to code the macro to say if there was a location number with a value but not a T.I.V value for the same location it would stop filling in the location number.

If that can't be done I understand but I thought I would ask to see if it was doable.

 

[par60056]

Look at the code I provided.

I took your sample table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1.1[/TD]
[TD]100[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]200[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]300[/TD]
[TD]stock[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[TD]200[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]400[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]600[/TD]
[TD]income[/TD]
[/TR]
</tbody>[/TABLE]

I modified the previous code to copy column A to column F and my column F looks like:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[/TR]
[TR]
[TD]2.1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tennisguuy]

I copied the new code and it works but it still putting a location value in cell AK600 when the last row of data that contains a location value, T.I.V value and coverage value is in row AK53. After AK53 it just continues to put 2.7 in every cell from AK54 to AK600 and I want it to stop filling in the location if there are no values in the other two cell(ie. T.I.V and coverage). In the example about it continues 2.1 all the way to cell 600 if I try it with that data sample.

 

[par60056]

ok as I said in the code, there are 3 ways to find the last row and each has issues.

Because you have already done something in row 600, UsedRange is wrong and thinks it should go to 600.

instead of:
lastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1

try:
lastRow = sh.UsedRange.End(xlUp).Row

 

[Tennisguuy]

Sorry I forgot about that. I replace the line as you suggested. I get a run time code. The error code is '424': object required

 

[par60056]

Sorry. I made a sheet variable so that the editor could autocomplete. Change "sh." to "Activesheet."

 

[Tennisguuy]

I replaced the code with the following:


Sub Macro4()
'
' Macro4 Macro
' Mascro to copy location numbers to column t for processing
'
' Keyboard Shortcut: Ctrl+p

' Find the last row
' There are 3 main ways to find the last row, each has issues.
' I prefer using UsedRange. While it can get messed up, it is usually right

Dim lastRow As Long

lastRow = ActiveSheet.UsedRange.End(xlUp).Row

'I dislike selecting things if it isn't needed.
Range("N2:N" & lastRow).Copy Range("AK2")
Range("AK2:AK" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub


It process but in cell AK1 in the location column it put a 0. It's also still putting the last location # all the way down to row 600

 

[Tennisguuy]

The last location number is what I meant.