Usuário com melhor resposta
Simplificar código

Pergunta
-
Boa noite
Será que tem uma maneira de simplificar o código abaixo, só na planilha 3 ele ficará gigante, tem mais 5 planilha para inserir os códigos.
Sub data() Plan3.Unprotect Password:="far" Plan3.Range("F13:IS513").Locked = True If Date = Range("F9") Then For L = 13 To 513 If Cells(L, 6) = "" And Cells(L, 7) = "" And Cells(L, 8) = "" And Cells(L, 9) = "" Then Plan3.Range("F" & L).Locked = False Else Plan3.Range("F" & L).Locked = True End If Next L ' For L = 13 To 513 If Cells(L, 7) = "" And Cells(L, 8) = "" And Cells(L, 9) = "" Then 'If Cells(L, 11) = "" Then Plan3.Range("G" & L).Locked = False Else Plan3.Range("G" & L).Locked = True End If Next L ' For L = 13 To 513 If Cells(L, 8) = "" And Cells(L, 9) = "" Then 'If Cells(L, 12) = "" Then Plan3.Range("H" & L).Locked = False Else Plan3.Range("H" & L).Locked = True End If Next L ' For L = 13 To 513 If Cells(L, 9) = "" Then Plan3.Range("I" & L).Locked = False Else Plan3.Range("I" & L).Locked = True End If Next L End If Plan3.Protect Password:="far" End Sub Sub data2() Plan3.Unprotect Password:="far" Plan3.Range("F13:IS513").Locked = True If Date = Range("J9") Then For L = 13 To 513 If Cells(L, 10) = "" And Cells(L, 11) = "" And Cells(L, 12) = "" And Cells(L, 13) = "" Then Plan3.Range("J" & L).Locked = False Else Plan3.Range("J" & L).Locked = True End If Next L ' For L = 13 To 513 If Cells(L, 11) = "" And Cells(L, 12) = "" And Cells(L, 13) = "" Then 'If Cells(L, 11) = "" Then Plan3.Range("K" & L).Locked = False Else Plan3.Range("K" & L).Locked = True End If Next L ' For L = 13 To 513 If Cells(L, 12) = "" And Cells(L, 13) = "" Then 'If Cells(L, 12) = "" Then Plan3.Range("L" & L).Locked = False Else Plan3.Range("L" & L).Locked = True End If Next L ' For L = 13 To 513 If Cells(L, 13) = "" Then Plan3.Range("M" & L).Locked = False Else Plan3.Range("M" & L).Locked = True End If Next L ' End If Plan3.Protect Password:="far" End Sub
Ai só 2 comparações que vai até 60 na data
comparações essa que vai de (F9; J9; N9; R9; V9; Z9; AD9; AH9; até IP9)
Obrigado pela atenção.
Respostas
Todas as Respostas
-
Simplifiquei o subprocedimento data para:
Sub data() Dim lngLin As Long Plan3.Unprotect Password:="far" Plan3.Range("F13:IS513").Locked = True If Date = Range("F9") Then For lngLin = 13 To 513 If Cells(lngLin, 6) = "" And Cells(lngLin, 7) = "" And Cells(lngLin, 8) = "" And Cells(lngLin, 9) = "" Then Plan3.Range("F" & lngLin).Locked = False End If If Cells(lngLin, 7) = "" And Cells(lngLin, 8) = "" And Cells(lngLin, 9) = "" Then Plan3.Range("G" & lngLin).Locked = False End If If Cells(lngLin, 8) = "" And Cells(lngLin, 9) = "" Then Plan3.Range("H" & lngLin).Locked = False End If If Cells(lngLin, 9) = "" Then Plan3.Range("I" & lngLin).Locked = False End If Next lngLin End If Plan3.Protect Password:="far" End Sub
Qual seria a continuação desse código? Não entendi a lógica.
Felipe Costa Gualberto - http://www.ambienteoffice.com.br
-
Acho que entendi. Experimente:
Sub data() Dim lngLin As Long Dim lngCol As Long Plan3.Unprotect Password:="far" Plan3.Range("F13:IS513").Locked = True For lngCol = 6 To 250 Step 4 If Cells(9, lngCol) = Date Then For lngLin = 13 To 513 If Cells(lngLin, lngCol) = "" _ And Cells(lngLin, lngCol + 1) = "" _ And Cells(lngLin, lngCol + 2) = "" _ And Cells(lngLin, lngCol + 3) = "" Then Plan3.Cells(lngLin, lngCol).Locked = False End If If Cells(lngLin, lngCol + 1) = "" _ And Cells(lngLin, lngCol + 2) = "" _ And Cells(lngLin, lngCol + 3) = "" Then Plan3.Cells(lngLin, lngCol + 1).Locked = False End If If Cells(lngLin, lngCol + 2) = "" _ And Cells(lngLin, lngCol + 3) = "" Then Plan3.Cells(lngLin, lngCol + 2).Locked = False End If If Cells(lngLin, lngCol + 3) = "" Then Plan3.Cells(lngLin, lngCol + 3).Locked = False End If Next lngLin End If Plan3.Protect Password:="far" End Sub
Felipe Costa Gualberto - http://www.ambienteoffice.com.br
-