Easy way to convert formula into vba/macro?

[Akira181]

I have a table of 52 columns 45 rows with a rather long formula in there to build the data. I'm having an issue where sometimes the data will need to be manually entered, which will delete the formula, which I don't want.

Is there an easy way to convert the formula for the whole table into VBA/Macro?

I tried recording a macro > highlighting the table > F2 > enter but it only worked for one cell. The formula is essentially identical throughout apart from the cell references.

 

[Akira181]

got another question, hopefully simple for you. Can the clear row part of the code be modified to only clear the cells that are greater (or smaller) than a Calendar Week displayed in Sheet1, G3?

  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57)) = ""   ' Clear entire row of week entries

 

[offthelip]

change that line of code to this:

   strcol = Range("G3")
   If strcol < 1 Or strcol = "" Then  ' a bit of error checking
    strcol = 1
   End If
  .Range(.Cells(rowno - 3, 5 + strcol), .Cells(rowno - 3, 57)) = "" ' Clear entire row of week entries

This does greater, I am not sure what you meant by greater or smaller, but you can use a similar type of limit on the "57" if necessary

 

[Akira181]

That works perfectly, thanks again! Not sure why I'm finding VBA so difficult. I understand what you've done thanks to the comments but coming up with the solution myself is a whole different matter.

I got a second VBA problem for a SUMIF / SUMIFS that I posted here (VBA code for SUMIFS function). It's the last half of this file I'm struggling to build.

It should be an easier problem than this one, if you have spare time to take a look, I'll be eternally grateful !