| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code work but a easier way to do this.
Hi to all. My code work nice but is a little long. I know that it could be
simplefy using value and other codes that I don't know. can anybody help me?? I have to run this from column C to N. Here is the code. Sub Run_Macro() monthfree = Range("C7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("C7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("C8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("C12").Select ActiveCell.FormulaR1C1 = Unidentified Else monthfree = Range("D7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("D7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("D8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("D12").Select ActiveCell.FormulaR1C1 = Unidentified Else monthfree = Range("E7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("E7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("E8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("E12").Select ActiveCell.FormulaR1C1 = Unidentified Else 'AGAIN AND AGAIN UNTIL COLUMN "N" End If End If End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code work but a easier way to do this.
** untested air code **
Dim wks as Worksheet Set wks = ActiveSheet For c = 3 to 14 'columns C to N If Len(wks.Cells(7,c).Value) = 0 Then Call Execute wks.Cells(7,c).FormulaR1C1 = wks.Range("A36").Value wks.Cells(8,c).FormulaR1C1 = wks.Range("A37").Value wks.Cells(12,c).FormulaR1C1 = wks.Range("A41").Value ' Exit loop after the first empty cell is encountered Exit For End If Next c You *could* also bracket the "For...Next" loop with a "With...End With" construct: With wks .... End With This would further simplify the code (and improve performance), but I chose not to do that here for clarity (i.e., too much information at one time), but feel free to look it up in VBA Help if you think you are ready for it. HTH, "Victor Torres" wrote in message ... Hi to all. My code work nice but is a little long. I know that it could be simplefy using value and other codes that I don't know. can anybody help me?? I have to run this from column C to N. Here is the code. Sub Run_Macro() monthfree = Range("C7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("C7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("C8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("C12").Select ActiveCell.FormulaR1C1 = Unidentified Else monthfree = Range("D7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("D7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("D8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("D12").Select ActiveCell.FormulaR1C1 = Unidentified Else monthfree = Range("E7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("E7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("E8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("E12").Select ActiveCell.FormulaR1C1 = Unidentified Else 'AGAIN AND AGAIN UNTIL COLUMN "N" End If End If End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code work but a easier way to do this.
George... YOU ARE THE BEST!!!! it works nice....
Thanks a lot "George Nicholson" wrote: ** untested air code ** Dim wks as Worksheet Set wks = ActiveSheet For c = 3 to 14 'columns C to N If Len(wks.Cells(7,c).Value) = 0 Then Call Execute wks.Cells(7,c).FormulaR1C1 = wks.Range("A36").Value wks.Cells(8,c).FormulaR1C1 = wks.Range("A37").Value wks.Cells(12,c).FormulaR1C1 = wks.Range("A41").Value ' Exit loop after the first empty cell is encountered Exit For End If Next c You *could* also bracket the "For...Next" loop with a "With...End With" construct: With wks .... End With This would further simplify the code (and improve performance), but I chose not to do that here for clarity (i.e., too much information at one time), but feel free to look it up in VBA Help if you think you are ready for it. HTH, "Victor Torres" wrote in message ... Hi to all. My code work nice but is a little long. I know that it could be simplefy using value and other codes that I don't know. can anybody help me?? I have to run this from column C to N. Here is the code. Sub Run_Macro() monthfree = Range("C7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("C7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("C8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("C12").Select ActiveCell.FormulaR1C1 = Unidentified Else monthfree = Range("D7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("D7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("D8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("D12").Select ActiveCell.FormulaR1C1 = Unidentified Else monthfree = Range("E7").Value If monthfree = Empty Then Call Execute SightPurch = Range("A36").Value Range("E7").Select ActiveCell.FormulaR1C1 = SightPurch InvoiceElite = Range("A37").Value Range("E8").Select ActiveCell.FormulaR1C1 = InvoiceElite Unidentified = Range("A41").Value Range("E12").Select ActiveCell.FormulaR1C1 = Unidentified Else 'AGAIN AND AGAIN UNTIL COLUMN "N" End If End If End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why does this code not work? | Excel Programming | |||
| Why Won't This Code Work???? | Excel Programming | |||
| Help getting code to work. | Excel Programming | |||
| Code won't work? | Excel Programming | |||
| Why my code do not work : - ( | Excel Programming | |||
Hybrid Mode
