Mental Math workbook - vba to copy down

[xluser2007]

Hi All,

It's been a while since I posted on here! Good to be back.

I am creating a simple workbook to give me some practice doing mental maths. Please see attached file.

In the worksheet "Tutorial 2" I'm applying a macro to simply copy down the formula cells (highlighted in yellow).

For some reason the macro is deleting the second column.

Could anyone please help me to fix this bug.

Thanks,

[snb]

No reason to post a zipped file...

use simply:


Sub snb()
For Each cl In Rows(5).SpecialCells(-4123)
cl.AutoFill cl.Resize(40)
Next
End Sub

[xluser2007]

Hi Snb!

Thanks for your code, but it does not quite do what I want. The revised code looks like this:

Public Sub ResetMentalMathCalcs(strName As String, strWbkName As String, strNumCalcs As String)

With Application
.ScreenUpdating = False
End With

dblNumCalcs = ThisWorkbook.Worksheets(strWbkName).Range(strNumCalcs).Value
ThisWorkbook.Worksheets(strWbkName).Range(strNumCalcs).Offset(1, 0).Resize(1000, 1).Clear
Debug.Print dblNumCalcs
For Each Rng In Rows(5).SpecialCells(-4123)
Rng.AutoFill Rng.Resize(40)
Next

End Sub

This uses autofill which I was trying to avoid, namely because the number generating formulas use RANDBETWEEN, which refreshes everytime I put in my answers due to its volatile nature. Ideally, I would like:

1. The random number formulas to simply be copied down and then repasted as values for the number of rows specified in "B2". Autofill also does not preserve the formats of the cells as I would like.
2. The check formulas should be copied down as formulas so that the dynamic check of answers is preserved, they begin with "=IF..." in columns F, M, T, AA.

Could you please assist with this? Sorry if I was not clearer in my original post.

Thanks,

[snb]

Is this what you are looking for ?


Sub snb()
For Each cl In Rows(5).SpecialCells(-4123)
If InStr(cl.Formula, "RAND") > 0 Then cl.Offset(1).Resize(50) = cl.Value
Next
End Sub

[xluser2007]

No, this appears to just copy down the same value of random numbers down (as opposed to different randomly generated values) and also does not copy down the "=IF(...) formulas down as formulas or the text values of "+" and "-".

I do like the brevity of your code and of it could be modified to do the above, I'm all for it.

My original code was doing the right thing, but kept deleting the contents of column B, can't understand why?

Many thanks for your continued help.

[snb]

Next try (only related to the 'rand' formulae):

Sub snb()
For Each cl In Rows(5).SpecialCells(-4123)
If InStr(cl.Formula, "RAND") Then
With cl.Offset(1).Resize(Cells(2, 2).Value)
.Formula = cl.Formula
.Value = .Value
End With
End If
Next
End Sub




2. The check formulas should be copied down as formulas so that the dynamic check of answers is preserved, they begin with "=IF..." in columns F, M, T, AA.



But once they have been copied down they don't need to be copied again ?
So why copying since the column has been filled already with these formulae.

[xluser2007]

Next try (only related to the 'rand' formulae):

Sub snb()
For Each cl In Rows(5).SpecialCells(-4123)
If InStr(cl.Formula, "RAND") Then
With cl.Offset(1).Resize(Cells(2, 2).Value)
.Formula = cl.Formula
.Value = .Value
End With
End If
Next
End Sub




This keeps throwing a runtime error 13, even when I correct the names of the variables based on my template.

I've attached the sample workbook for your reference, could you please take a look at it and see if I've made any errors implementing your code?


But once they have been copied down they don't need to be copied again ?
So why copying since the column has been filled already with these formulae.

Because the number of rows they need to be copied down is variable based on the value in cell B2 i.e. if 25 then everything below the yellow row should be cleared and these formulas copied down 25 rows below the yellow formula rows.

Thanks again,

[snb]

You need to remove 'option explicit' or to disable it, using a apostrophe.

if you use in column E the formula


=IF(A11="";"";N(A11+C11*(2*N(B11="+")-1)=E11))

You won't need to copy it down every time.

[xluser2007]

You need to remove 'option explicit' or to disable it, using a apostrophe.

Tried it, works fine for the random numbers but doesn't copy down "+", "-" etc and does not copy the "=IF(..." formulae down for the specified rows. Any thoughts on adding these features in your code?

[BrianMH]

Sub snb()
Dim cl As Range
For Each cl In Rows(5).Cells
If InStr(cl.Formula, "RAND") Then
With cl.Offset(1).Resize(Cells(2, 2).Value)
.Formula = cl.Formula
.Value = .Value
End With
ElseIf Not cl.Formula = "" And Not cl.Formula Like "*RAND*" Then
With cl.Offset(1).Resize(Cells(2, 2).Value)
.Formula = cl.Formula
End With
End If
Next
End Sub


This work?

[xluser2007]

This work?

This is very close! But for the "IF(..." formulas, the formula for each row below references cells in the row immediately above i.e. the formula is not being copied down as intended.

In the attached file with your revised code run through, see cell "F6", whch references cells from row 5 as opposed to row 6.

Any ideas on how to correct the code for this?

[BrianMH]

Sub snb()
Dim cl As Range
For Each cl In Rows(5).Cells
If InStr(cl.Formula, "RAND") Then
With cl.Offset(1).Resize(Cells(2, 2).Value)
.Formula = cl.Formula
.Value = .Value
End With
ElseIf Not cl.Formula = "" And Not cl.Formula Like "*RAND*" Then
cl.AutoFill cl.Resize(Cells(2, 2).Value + 1), xlFillCopy
End If
Next
End Sub

[xluser2007]

Thanks Brian, this fixes the problem however the autofill drags down the yellow cell formats for "+", "-" and "IF(..." cells, where as I would like just the formulas copied down for these without formats.

Any thoughts?

Thanks

[snb]

Did you try the formula I provided ?

[xluser2007]

Did you try the formula I provided ?

Sorry, didn't see that.

I pasted it, but what do you "you won;t need to copy it down everytime". Not sure I get you.

With VBA, my intention was for the code to copy the cehck formula down everytime to the number of specified rows in B2, as that is how many calculations the user would like to undertake.

Could you please explain what you mean?

My original code was working but kept deleting the second column. Any ideas how to correct my original code to avoid this issue?

[BrianMH]

Try changing xlFillCopy to xlFillValues and see if that works.

[xluser2007]

Try changing xlFillCopy to xlFillValues and see if that works.


I've actually changed my workbook setup to incorporate your and snb's code better. Please see attached workbook.

The code works now (yay!) but is too slow. Could you please let me know if it can be made more efficient?

Thanks,

Shamindra

[BrianMH]

Is there a reason you didn't use the vba as it was posted? It worked very quickly for me.

[xluser2007]

Is there a reason you didn't use the vba as it was posted? It worked very quickly for me.

Yeah, because I clear the range before copying down the values and formulae. This removes all values and formats. It is important to clear everything as the user will specify the number of rows so we only want cells to be updated with correct values/ formats/ formulae for the specified number of rows, so we 'clear the field' before doing any of this.

I want the formats to remain for everything so I decided to use autofill as a first pass.

Afterwords, I used the code you had given to convert the "RAND" formulae to values as a second pass.

Does this make sense, is there a better way to do this?

It is the autofil that is taking a long time, which is baffling me - normally that is the fastest way to copy/ paste in excel.


For Each Rng In Rows(5).Cells
Rng.AutoFill Rng.Resize(Cells(2, 2).Value + 1)
Next

I tried changing it to:

Application.Calculation = xlCalculationManual

For Each Rng In Rows(5).Cells
Rng.AutoFill Rng.Resize(Cells(2, 2).Value + 1)
Next
Application.Calculate


But still very slow - any ideas why and how to correct?

[xluser2007]

Ok, worked it out, changed the code to:

For Each Rng In ThisWorkbook.Worksheets(strWkshtName).Range(strName)
Rng.AutoFill Rng.Resize(Cells(2, 2).Value + 1)
Next

Instead of processing across every cell in row 5.

I do have one more question, any idea how to dynamically set the print area to go down to the last row as based on user input in cell B2?

Thanks for all your help

[BrianMH]

Sub snb()
Dim cl As Range
ThisWorkbook.Sheets(1).Range("A6:AD1000").Clear
For Each cl In Rows(5).Cells
If InStr(cl.Formula, "RAND") Then

With cl.Offset(1).Resize(Cells(2, 2).Value)
.Formula = cl.Formula
.Value = .Value
cl.AutoFill cl.Resize(Cells(2, 2).Value + 1), xlFillFormats
End With
ElseIf Not cl.Formula = "" And Not cl.Formula Like "*RAND*" Then
cl.AutoFill cl.Resize(Cells(2, 2).Value + 1), xlFillCopy
ElseIf cl.Borders.LineStyle = 1 And cl.Formula = "" Then
cl.AutoFill cl.Resize(Cells(2, 2).Value + 1), xlFillFormats
End If
Next
End Sub

[xluser2007]

That's very quick also - appreciate it!

Any ideas on making the print area dynamic with the rows?

[BrianMH]

ThisWorkbook.Worksheets(strWkshtName).PrintArea = ThisWorkbook.Worksheets(strWkshtName).Range(strName).Resize(Cells(2, 2).Value + 1)

add that at the end.

[xluser2007]

ThisWorkbook.Worksheets(strWkshtName).PrintArea = ThisWorkbook.Worksheets(strWkshtName).Range(strName).Resize(Cells(2, 2).Value + 1)

add that at the end.

I changed it to the following, as your code was giving errors:

ThisWorkbook.Worksheets(strWkshtName).PageSetup.PrintArea = ThisWorkbook.Worksheets(strWkshtName).Range(strName).Resize(Cells(2, 2).Value + 1).Address

Thanks for helping