Alguien me podria apoyar con resumir las operaciones ya que desarrolle las operaciones en un rango de filas por ejemplo hasta 45, sin embargo muchas hojas son variables y la formula a veces es mas de 45 hasta 100 o 200 filas, por lo que se demora un poco en las operaciones, habra alguna forma de que las operaciones se hagan en funcion de la cantidad de datos de determinada columna
SAludos,
Sub ProcesarMO()
Dim celda As Object
Dim rng As Range
Set rng = Range("I13:FE13")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G13")) * Val(Range("H13")) / 9.5
Next celda
Set rng = Range("I14:FE14")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G14")) * Val(Range("H14")) / 9.5
Next celda
Set rng = Range("I15:FE15")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G15")) * Val(Range("H15")) / 9.5
Next celda
Set rng = Range("I16:FE16")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G16")) * Val(Range("H16")) / 9.5
Next celda
Set rng = Range("I17:FE17")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G17")) * Val(Range("H17")) / 9.5
Next celda
Set rng = Range("I18:FE18")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G18")) * Val(Range("H18")) / 9.5
Next celda
Set rng = Range("I19:FE19")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G19")) * Val(Range("H19")) / 9.5
Next celda
Set rng = Range("I20:FE20")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G20")) * Val(Range("H20")) / 9.5
Next celda
Set rng = Range("I21:FE21")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G21")) * Val(Range("H21")) / 9.5
Next celda
Set rng = Range("I22:FE22")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G22")) * Val(Range("H22")) / 9.5
Next celda
Set rng = Range("I23:FE23")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G23")) * Val(Range("H23")) / 9.5
Next celda
Set rng = Range("I24:FE24")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G24")) * Val(Range("H24")) / 9.5
Next celda
Set rng = Range("I25:FE25")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G25")) * Val(Range("H25")) / 9.5
Next celda
Set rng = Range("I26:FE26")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G26")) * Val(Range("H26")) / 9.5
Next celda
Set rng = Range("I27:FE27")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G27")) * Val(Range("H27")) / 9.5
Next celda
Set rng = Range("I28:FE28")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G28")) * Val(Range("H28")) / 9.5
Next celda
Set rng = Range("I29:FE29")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G29")) * Val(Range("H29")) / 9.5
Next celda
Set rng = Range("I30:FE30")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G30")) * Val(Range("H30")) / 9.5
Next celda
Set rng = Range("I31:FE31")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G31")) * Val(Range("H31")) / 9.5
Next celda
Set rng = Range("I32:FE32")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G32")) * Val(Range("H32")) / 9.5
Next celda
Set rng = Range("I33:FE33")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G33")) * Val(Range("H33")) / 9.5
Next celda
Set rng = Range("I34:FE34")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G34")) * Val(Range("H34")) / 9.5
Next celda
Set rng = Range("I35:FE35")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G35")) * Val(Range("H35")) / 9.5
Next celda
Set rng = Range("I36:FE36")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G36")) * Val(Range("H36")) / 9.5
Next celda
Set rng = Range("I37:FE37")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G37")) * Val(Range("H37")) / 9.5
Next celda
Set rng = Range("I38:FE38")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G38")) * Val(Range("H38")) / 9.5
Next celda
Set rng = Range("I39:FE39")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G39")) * Val(Range("H39")) / 9.5
Next celda
Set rng = Range("I40:FE40")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G40")) * Val(Range("H40")) / 9.5
Next celda
Set rng = Range("I41:FE41")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G41")) * Val(Range("H41")) / 9.5
Next celda
Set rng = Range("I42:FE42")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G42")) * Val(Range("H42")) / 9.5
Next celda
Set rng = Range("I43:FE43")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G43")) * Val(Range("H43")) / 9.5
Next celda
Set rng = Range("I44:FE44")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G44")) * Val(Range("H44")) / 9.5
Next celda
Set rng = Range("I45:FE45")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G45")) * Val(Range("H45")) / 9.5
Next celda
''''''
Application.ScreenUpdating = True
MsgBox "Calculo de M.O Exitoso", vbInformation
Exit Sub
End Sub
Featured Replies
Archivado
Este tema está ahora archivado y está cerrado a más respuestas.
Buenas Tardes,
Alguien me podria apoyar con resumir las operaciones ya que desarrolle las operaciones en un rango de filas por ejemplo hasta 45, sin embargo muchas hojas son variables y la formula a veces es mas de 45 hasta 100 o 200 filas, por lo que se demora un poco en las operaciones, habra alguna forma de que las operaciones se hagan en funcion de la cantidad de datos de determinada columna
SAludos,
Sub ProcesarMO()
Dim celda As Object
Dim rng As Range
Set rng = Range("I13:FE13")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G13")) * Val(Range("H13")) / 9.5
Next celda
Set rng = Range("I14:FE14")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G14")) * Val(Range("H14")) / 9.5
Next celda
Set rng = Range("I15:FE15")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G15")) * Val(Range("H15")) / 9.5
Next celda
Set rng = Range("I16:FE16")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G16")) * Val(Range("H16")) / 9.5
Next celda
Set rng = Range("I17:FE17")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G17")) * Val(Range("H17")) / 9.5
Next celda
Set rng = Range("I18:FE18")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G18")) * Val(Range("H18")) / 9.5
Next celda
Set rng = Range("I19:FE19")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G19")) * Val(Range("H19")) / 9.5
Next celda
Set rng = Range("I20:FE20")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G20")) * Val(Range("H20")) / 9.5
Next celda
Set rng = Range("I21:FE21")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G21")) * Val(Range("H21")) / 9.5
Next celda
Set rng = Range("I22:FE22")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G22")) * Val(Range("H22")) / 9.5
Next celda
Set rng = Range("I23:FE23")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G23")) * Val(Range("H23")) / 9.5
Next celda
Set rng = Range("I24:FE24")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G24")) * Val(Range("H24")) / 9.5
Next celda
Set rng = Range("I25:FE25")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G25")) * Val(Range("H25")) / 9.5
Next celda
Set rng = Range("I26:FE26")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G26")) * Val(Range("H26")) / 9.5
Next celda
Set rng = Range("I27:FE27")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G27")) * Val(Range("H27")) / 9.5
Next celda
Set rng = Range("I28:FE28")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G28")) * Val(Range("H28")) / 9.5
Next celda
Set rng = Range("I29:FE29")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G29")) * Val(Range("H29")) / 9.5
Next celda
Set rng = Range("I30:FE30")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G30")) * Val(Range("H30")) / 9.5
Next celda
Set rng = Range("I31:FE31")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G31")) * Val(Range("H31")) / 9.5
Next celda
Set rng = Range("I32:FE32")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G32")) * Val(Range("H32")) / 9.5
Next celda
Set rng = Range("I33:FE33")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G33")) * Val(Range("H33")) / 9.5
Next celda
Set rng = Range("I34:FE34")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G34")) * Val(Range("H34")) / 9.5
Next celda
Set rng = Range("I35:FE35")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G35")) * Val(Range("H35")) / 9.5
Next celda
Set rng = Range("I36:FE36")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G36")) * Val(Range("H36")) / 9.5
Next celda
Set rng = Range("I37:FE37")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G37")) * Val(Range("H37")) / 9.5
Next celda
Set rng = Range("I38:FE38")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G38")) * Val(Range("H38")) / 9.5
Next celda
Set rng = Range("I39:FE39")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G39")) * Val(Range("H39")) / 9.5
Next celda
Set rng = Range("I40:FE40")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G40")) * Val(Range("H40")) / 9.5
Next celda
Set rng = Range("I41:FE41")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G41")) * Val(Range("H41")) / 9.5
Next celda
Set rng = Range("I42:FE42")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G42")) * Val(Range("H42")) / 9.5
Next celda
Set rng = Range("I43:FE43")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G43")) * Val(Range("H43")) / 9.5
Next celda
Set rng = Range("I44:FE44")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G44")) * Val(Range("H44")) / 9.5
Next celda
Set rng = Range("I45:FE45")
For Each celda In rng
valor = celda.Value
If valor Like "*S*" Then celda = Val(Range("G45")) * Val(Range("H45")) / 9.5
Next celda
''''''
Application.ScreenUpdating = True
MsgBox "Calculo de M.O Exitoso", vbInformation
Exit Sub
End Sub