changing range A1:A2 to have number instead of column letter reference vba

[drop05]

Hello i have the below formula and would like to edit it to get it into a for loop

MainPage.Range("15:15" & LastColumn).Locked = False
MainPage.Range("15:15" & LastColumn).FormulaHidden = False

the way i would like to edit it is instead of just doing the whole row from i would like to do it with the columns as well but the column is referenced with an integer

so it would be like
MainPage.Range("D15:D15").Locked = False
MainPage.Range("D15:D15").FormulaHidden = False
MainPage.Range("E15:E15").Locked = False
MainPage.Range("E15:E15").FormulaHidden = False


MainPage.Range("P15:P15").Locked = False
MainPage.Range("P15:P15").FormulaHidden = False
MainPage.Range("Q15:Q15").Locked = False
MainPage.Range("Q15:Q15").FormulaHidden = False

The column would be the integer so "i"

but not sure how to write it like that where i am not putting the column letter would rather it be a number reference so i can write some math on what "i" will be or if needing to add other integers to do the math

 

[Eric W]

Try:

r = 15
c = 16
MainPage.Cells(r, c).Locked = False

If you have multiple consecutive columns, you can do something like:

r = 15
c = 16
n = 5
MainPage.Cells(r, c).Resize(, n).Locked = False

 

[drop05]

I see, i had logic using cells before but kept getting a run time error 1004 for it, here is my logic below if you could maybe see what i did wrong.
I am getting the run time error here on this line:
main1.Cells(rowNumCol + rowMult, (((i - 1) * (entColAmtCol)) + colNum) + FirstColOffFourCol).Locked = False

What my code is doing is it is unlocking a cell, the cell is merged so i figured just do it for the two cells in which got merged, but it will loop through and go X amount of rows down and X amount of columns over, so should go through the first batch of columns which will have another for loop inside it to go through X amount of rows then go to the next batch column do the same with the rows and so on

is it because of the .cells with the .locked? i saw a lot of .range with .locked is all?

Sub Unprotect()

Dim entColAmtCol, colNum, SecColOffFourCol, FirstColOffFourCol, rowMultCol, rowNumCol, SG_RowMultCol, rowMult, groupNum, sg, iterAmtCol, i As Integer

Dim page1 As Workbook
Set page1 = Application.ThisWorkbook
Dim page1_ws As Worksheet

Dim page1_list As Worksheet
Set page1_list = page1.Worksheets("List")

Dim num_ent As Integer
num_ent = page1_list.Cells(Rows.count, 2).End(xlUp).Row - 4

Dim main1 As Worksheet
Set main1 = page1.Worksheets("Main")

entColAmtCol = 4
colNum = 4
FirstColOffFourCol = 13
SecColOffFourCol = 14
rowNumCol = 15
rowMultCol = 66
rowMult = 0
iterAmtCol = 11

For i = 1 To num_ent
    For groupNum = 1 To iterAmtCol
    
        main1.Cells(rowNumCol + rowMult, (((i - 1) * (entColAmtCol)) + colNum) + FirstColOffFourCol).Locked = False
        main1.Cells(rowNumCol + rowMult, (((i - 1) * (entColAmtCol)) + colNum) + SecColOffFourCol).FormulaHidden = False
    
        rowMult = rowMult + rowMultCol

    Next groupNum
Next i

End Sub

Next i

 

[Eric W]

Well, your extract has a "Next i" after the End Sub. I assume that's just an erroneous line in the print out. As far as the rest of the macro, I copied into my workbook, renamed a couple of sheets to Main and List, and I ran it. It ran successfully without errors. So I'd guess your problem is something with your workbook. I also just set the num_ent value to 5. I'd just recommend you run the macro again, and when it hits the error, figure out what all the values are and see if they are valid. Since you're dealing with the Locked attribute, you might experiment with protecting/unprotecting the sheet. Good luck!

 

[drop05]

Well, your extract has a "Next i" after the End Sub. I assume that's just an erroneous line in the print out. As far as the rest of the macro, I copied into my workbook, renamed a couple of sheets to Main and List, and I ran it. It ran successfully without errors. So I'd guess your problem is something with your workbook. I also just set the num_ent value to 5. I'd just recommend you run the macro again, and when it hits the error, figure out what all the values are and see if they are valid. Since you're dealing with the Locked attribute, you might experiment with protecting/unprotecting the sheet. Good luck!

Thank you! Yes i will go step by step, maybe i mis-spelled something or had a leading space thank you!

 

[drop05]

Well, your extract has a "Next i" after the End Sub. I assume that's just an erroneous line in the print out. As far as the rest of the macro, I copied into my workbook, renamed a couple of sheets to Main and List, and I ran it. It ran successfully without errors. So I'd guess your problem is something with your workbook. I also just set the num_ent value to 5. I'd just recommend you run the macro again, and when it hits the error, figure out what all the values are and see if they are valid. Since you're dealing with the Locked attribute, you might experiment with protecting/unprotecting the sheet. Good luck!

Yes sorry for the next next i accidently copied and pasted it.

 

[drop05]

Well, your extract has a "Next i" after the End Sub. I assume that's just an erroneous line in the print out. As far as the rest of the macro, I copied into my workbook, renamed a couple of sheets to Main and List, and I ran it. It ran successfully without errors. So I'd guess your problem is something with your workbook. I also just set the num_ent value to 5. I'd just recommend you run the macro again, and when it hits the error, figure out what all the values are and see if they are valid. Since you're dealing with the Locked attribute, you might experiment with protecting/unprotecting the sheet. Good luck!

I am getting an unable to set the locked property of the range class of the line

main1.Cells(rowNumCol + rowMult, (((i - 1) * (entColAmtCol)) + colNum) + FirstColOffFourCol).Locked = False

i am not sure why this is happening and that it worked on your side

 

[drop05]

Well, your extract has a "Next i" after the End Sub. I assume that's just an erroneous line in the print out. As far as the rest of the macro, I copied into my workbook, renamed a couple of sheets to Main and List, and I ran it. It ran successfully without errors. So I'd guess your problem is something with your workbook. I also just set the num_ent value to 5. I'd just recommend you run the macro again, and when it hits the error, figure out what all the values are and see if they are valid. Since you're dealing with the Locked attribute, you might experiment with protecting/unprotecting the sheet. Good luck!

was able to get it to work with no errors! Added a .MergeArea before the .locked and before the .formulahidden and it worked!
thank you again