| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Letters in the code
Hi All, i am trying to figure out how to modify the code but i cam accross a
few letter i don't know what they are. can you please help me to determine what these letters are in the below code: Letter: e, a1, a, b1, b, c1, c, m, rr, rrbc, x -------Beginning of Code-------------- Private Sub RR_Calculation_Click() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Response = MsgBox(prompt:="Is this POP going to be extended?", Buttons:=vbYesNo) If Response = vbYes Then Ext = 0 Else Ext = 1 End If whr = Sheet31.Range("Work_Hours_Requirement") For Each e In Range("F15:F5000") a1 = Cells(e.Row, 14) If IsDate(a1) Then a = DateValue(a1) Else a = 0 End If b1 = Cells(e.Row, 15) If IsDate(b1) Then b = DateValue(b1) Else b = 0 End If c1 = Cells(e.Row, 45) If IsDate(c1) Then c = DateValue(c1) Else c = 0 End If m = 0 rr = 0 remob = 0 rrbc = 0 x = 0 If Cells(e.Row, 6).Value < "" Then If a = 0 Or b = 0 Or a b Or a = b Or Not IsDate(a) Or Not IsDate(b) Or a1 < a Or b1 < b Then MsgBox ("Please correct ETC Start and End dates (row " & e.Row & ") and and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub ElseIf Left(Cells(e.Row, 6), 3) = "HOU" Or Left(Cells(e.Row, 6), 3) = "HCN" Or Cells(e.Row, 12).Value < whr Then ElseIf c = 0 Or b = c Or Not IsDate(c) Or c1 < c Then MsgBox ("Please correct the Contract date (row " & e.Row & ") and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub Else Do While c < a If m = 2 Then c = c + 125 m = 0 x = x + 1 Else c = c + 120 m = m + 1 x = x + 1 End If Loop If x < 0 Then If m = 0 Then remob = remob + 1 Else rr = rr + 1 End If End If Do While c <= b + 1 If b + 1 - c < 30 And Ext = 1 And x < 0 Then If m = 0 Then remob = remob - 1 Else rr = rr - 1 End If rrbc = rrbc + 1 x = x + 1 End If If m = 2 Then c = c + 125 remob = remob + 1 m = 0 x = x + 1 Else c = c + 120 rr = rr + 1 m = m + 1 x = x + 1 End If Loop If m = 0 And x < 0 Then remob = remob - 1 ElseIf x < 0 Then rr = rr - 1 End If End If End If If Cells(e.Row, 9) = 0 Or Cells(e.Row, 9).Value = "" Then Cells(e.Row, 46).Value = 0 Cells(e.Row, 47).Value = 0 Cells(e.Row, 48).Value = 0 Cells(e.Row, 52).Value = 0 Else Cells(e.Row, 46).Value = rr + remob + rrbc Cells(e.Row, 47).Value = rr Cells(e.Row, 48).Value = remob Cells(e.Row, 52).Value = rrbc End If Next e Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True MsgBox (" Done!") End Sub ----------------------End--------------------------- |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Letters in the code
e, a1, a, b1, b, c1, c, m, rr, rrbc, x
e - Is a range object used in the loop For Each e In Range("F15:F5000") The rest are program variables that haven't been dimensioned anywhere I can see a1, a, b1, b, c1, c, m, rr, rrbc, x Despite this we can make a guess as to what they represent or at least what the writer of the code hoped they would. a1 is tested to see if it's a date so we can guess that's what the coder wanted If a1 is a date then the variable a is set to that date The same is then repeated for B1 & B - C1 & C The rest look like 'long' or 'integer' variables variables I think you may be better telling us what you want to do and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Gladiator" wrote: Hi All, i am trying to figure out how to modify the code but i cam accross a few letter i don't know what they are. can you please help me to determine what these letters are in the below code: Letter: e, a1, a, b1, b, c1, c, m, rr, rrbc, x -------Beginning of Code-------------- Private Sub RR_Calculation_Click() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Response = MsgBox(prompt:="Is this POP going to be extended?", Buttons:=vbYesNo) If Response = vbYes Then Ext = 0 Else Ext = 1 End If whr = Sheet31.Range("Work_Hours_Requirement") For Each e In Range("F15:F5000") a1 = Cells(e.Row, 14) If IsDate(a1) Then a = DateValue(a1) Else a = 0 End If b1 = Cells(e.Row, 15) If IsDate(b1) Then b = DateValue(b1) Else b = 0 End If c1 = Cells(e.Row, 45) If IsDate(c1) Then c = DateValue(c1) Else c = 0 End If m = 0 rr = 0 remob = 0 rrbc = 0 x = 0 If Cells(e.Row, 6).Value < "" Then If a = 0 Or b = 0 Or a b Or a = b Or Not IsDate(a) Or Not IsDate(b) Or a1 < a Or b1 < b Then MsgBox ("Please correct ETC Start and End dates (row " & e.Row & ") and and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub ElseIf Left(Cells(e.Row, 6), 3) = "HOU" Or Left(Cells(e.Row, 6), 3) = "HCN" Or Cells(e.Row, 12).Value < whr Then ElseIf c = 0 Or b = c Or Not IsDate(c) Or c1 < c Then MsgBox ("Please correct the Contract date (row " & e.Row & ") and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub Else Do While c < a If m = 2 Then c = c + 125 m = 0 x = x + 1 Else c = c + 120 m = m + 1 x = x + 1 End If Loop If x < 0 Then If m = 0 Then remob = remob + 1 Else rr = rr + 1 End If End If Do While c <= b + 1 If b + 1 - c < 30 And Ext = 1 And x < 0 Then If m = 0 Then remob = remob - 1 Else rr = rr - 1 End If rrbc = rrbc + 1 x = x + 1 End If If m = 2 Then c = c + 125 remob = remob + 1 m = 0 x = x + 1 Else c = c + 120 rr = rr + 1 m = m + 1 x = x + 1 End If Loop If m = 0 And x < 0 Then remob = remob - 1 ElseIf x < 0 Then rr = rr - 1 End If End If End If If Cells(e.Row, 9) = 0 Or Cells(e.Row, 9).Value = "" Then Cells(e.Row, 46).Value = 0 Cells(e.Row, 47).Value = 0 Cells(e.Row, 48).Value = 0 Cells(e.Row, 52).Value = 0 Else Cells(e.Row, 46).Value = rr + remob + rrbc Cells(e.Row, 47).Value = rr Cells(e.Row, 48).Value = remob Cells(e.Row, 52).Value = rrbc End If Next e Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True MsgBox (" Done!") End Sub ----------------------End--------------------------- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Letters in the code
Mike, my plan is to copy the codes into another tempalte to use. But since
the template has a totally different layout, i wanted to adjust the codes to fit the layout. But let me ask you this first: when the code says 'Cells(e.Row, 14)' or 'Cells(e.Row, 15)', does it refer to the row number in range F15:F5000 as mentioned in 'For Each e In Range("F15:F5000")'? So, 'Cells(e.Row, 45)' means 'Cell(Row15, Column45)', correct? Thanks. "Mike H" wrote: e, a1, a, b1, b, c1, c, m, rr, rrbc, x e - Is a range object used in the loop For Each e In Range("F15:F5000") The rest are program variables that haven't been dimensioned anywhere I can see a1, a, b1, b, c1, c, m, rr, rrbc, x Despite this we can make a guess as to what they represent or at least what the writer of the code hoped they would. a1 is tested to see if it's a date so we can guess that's what the coder wanted If a1 is a date then the variable a is set to that date The same is then repeated for B1 & B - C1 & C The rest look like 'long' or 'integer' variables variables I think you may be better telling us what you want to do and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Gladiator" wrote: Hi All, i am trying to figure out how to modify the code but i cam accross a few letter i don't know what they are. can you please help me to determine what these letters are in the below code: Letter: e, a1, a, b1, b, c1, c, m, rr, rrbc, x -------Beginning of Code-------------- Private Sub RR_Calculation_Click() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Response = MsgBox(prompt:="Is this POP going to be extended?", Buttons:=vbYesNo) If Response = vbYes Then Ext = 0 Else Ext = 1 End If whr = Sheet31.Range("Work_Hours_Requirement") For Each e In Range("F15:F5000") a1 = Cells(e.Row, 14) If IsDate(a1) Then a = DateValue(a1) Else a = 0 End If b1 = Cells(e.Row, 15) If IsDate(b1) Then b = DateValue(b1) Else b = 0 End If c1 = Cells(e.Row, 45) If IsDate(c1) Then c = DateValue(c1) Else c = 0 End If m = 0 rr = 0 remob = 0 rrbc = 0 x = 0 If Cells(e.Row, 6).Value < "" Then If a = 0 Or b = 0 Or a b Or a = b Or Not IsDate(a) Or Not IsDate(b) Or a1 < a Or b1 < b Then MsgBox ("Please correct ETC Start and End dates (row " & e.Row & ") and and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub ElseIf Left(Cells(e.Row, 6), 3) = "HOU" Or Left(Cells(e.Row, 6), 3) = "HCN" Or Cells(e.Row, 12).Value < whr Then ElseIf c = 0 Or b = c Or Not IsDate(c) Or c1 < c Then MsgBox ("Please correct the Contract date (row " & e.Row & ") and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub Else Do While c < a If m = 2 Then c = c + 125 m = 0 x = x + 1 Else c = c + 120 m = m + 1 x = x + 1 End If Loop If x < 0 Then If m = 0 Then remob = remob + 1 Else rr = rr + 1 End If End If Do While c <= b + 1 If b + 1 - c < 30 And Ext = 1 And x < 0 Then If m = 0 Then remob = remob - 1 Else rr = rr - 1 End If rrbc = rrbc + 1 x = x + 1 End If If m = 2 Then c = c + 125 remob = remob + 1 m = 0 x = x + 1 Else c = c + 120 rr = rr + 1 m = m + 1 x = x + 1 End If Loop If m = 0 And x < 0 Then remob = remob - 1 ElseIf x < 0 Then rr = rr - 1 End If End If End If If Cells(e.Row, 9) = 0 Or Cells(e.Row, 9).Value = "" Then Cells(e.Row, 46).Value = 0 Cells(e.Row, 47).Value = 0 Cells(e.Row, 48).Value = 0 Cells(e.Row, 52).Value = 0 Else Cells(e.Row, 46).Value = rr + remob + rrbc Cells(e.Row, 47).Value = rr Cells(e.Row, 48).Value = remob Cells(e.Row, 52).Value = rrbc End If Next e Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True MsgBox (" Done!") End Sub ----------------------End--------------------------- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Letters in the code
Hi,
In the code e is a range object and one property of a range object is ROW. Therefore e.row is the row number of the row the code is in while executing the loop For Each e In Range("F15:F5000") so e.row can hold any value from 15 to 5000 Cells(e.Row, 45) Is therefore an address in column 45 or column "AS". So at the very start of the loop it is Cells(15,45) or range("AS15") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Gladiator" wrote: Mike, my plan is to copy the codes into another tempalte to use. But since the template has a totally different layout, i wanted to adjust the codes to fit the layout. But let me ask you this first: when the code says 'Cells(e.Row, 14)' or 'Cells(e.Row, 15)', does it refer to the row number in range F15:F5000 as mentioned in 'For Each e In Range("F15:F5000")'? So, 'Cells(e.Row, 45)' means 'Cell(Row15, Column45)', correct? Thanks. "Mike H" wrote: e, a1, a, b1, b, c1, c, m, rr, rrbc, x e - Is a range object used in the loop For Each e In Range("F15:F5000") The rest are program variables that haven't been dimensioned anywhere I can see a1, a, b1, b, c1, c, m, rr, rrbc, x Despite this we can make a guess as to what they represent or at least what the writer of the code hoped they would. a1 is tested to see if it's a date so we can guess that's what the coder wanted If a1 is a date then the variable a is set to that date The same is then repeated for B1 & B - C1 & C The rest look like 'long' or 'integer' variables variables I think you may be better telling us what you want to do and someone will help -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Gladiator" wrote: Hi All, i am trying to figure out how to modify the code but i cam accross a few letter i don't know what they are. can you please help me to determine what these letters are in the below code: Letter: e, a1, a, b1, b, c1, c, m, rr, rrbc, x -------Beginning of Code-------------- Private Sub RR_Calculation_Click() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Response = MsgBox(prompt:="Is this POP going to be extended?", Buttons:=vbYesNo) If Response = vbYes Then Ext = 0 Else Ext = 1 End If whr = Sheet31.Range("Work_Hours_Requirement") For Each e In Range("F15:F5000") a1 = Cells(e.Row, 14) If IsDate(a1) Then a = DateValue(a1) Else a = 0 End If b1 = Cells(e.Row, 15) If IsDate(b1) Then b = DateValue(b1) Else b = 0 End If c1 = Cells(e.Row, 45) If IsDate(c1) Then c = DateValue(c1) Else c = 0 End If m = 0 rr = 0 remob = 0 rrbc = 0 x = 0 If Cells(e.Row, 6).Value < "" Then If a = 0 Or b = 0 Or a b Or a = b Or Not IsDate(a) Or Not IsDate(b) Or a1 < a Or b1 < b Then MsgBox ("Please correct ETC Start and End dates (row " & e.Row & ") and and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub ElseIf Left(Cells(e.Row, 6), 3) = "HOU" Or Left(Cells(e.Row, 6), 3) = "HCN" Or Cells(e.Row, 12).Value < whr Then ElseIf c = 0 Or b = c Or Not IsDate(c) Or c1 < c Then MsgBox ("Please correct the Contract date (row " & e.Row & ") and re-run the R&R Calculation!") Application.Calculation = xlCalculationAutomatic Exit Sub Else Do While c < a If m = 2 Then c = c + 125 m = 0 x = x + 1 Else c = c + 120 m = m + 1 x = x + 1 End If Loop If x < 0 Then If m = 0 Then remob = remob + 1 Else rr = rr + 1 End If End If Do While c <= b + 1 If b + 1 - c < 30 And Ext = 1 And x < 0 Then If m = 0 Then remob = remob - 1 Else rr = rr - 1 End If rrbc = rrbc + 1 x = x + 1 End If If m = 2 Then c = c + 125 remob = remob + 1 m = 0 x = x + 1 Else c = c + 120 rr = rr + 1 m = m + 1 x = x + 1 End If Loop If m = 0 And x < 0 Then remob = remob - 1 ElseIf x < 0 Then rr = rr - 1 End If End If End If If Cells(e.Row, 9) = 0 Or Cells(e.Row, 9).Value = "" Then Cells(e.Row, 46).Value = 0 Cells(e.Row, 47).Value = 0 Cells(e.Row, 48).Value = 0 Cells(e.Row, 52).Value = 0 Else Cells(e.Row, 46).Value = rr + remob + rrbc Cells(e.Row, 47).Value = rr Cells(e.Row, 48).Value = remob Cells(e.Row, 52).Value = rrbc End If Next e Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True MsgBox (" Done!") End Sub ----------------------End--------------------------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| custom number format code using letters and numbers | Excel Worksheet Functions | |||
| Custom Formating Letters to Numbers while Displaying Letters | Excel Worksheet Functions | |||
| how to change small letters to capital letters | Excel Discussion (Misc queries) | |||
| How do change a column of data in capitol letters to small letters | Excel Discussion (Misc queries) | |||
| Capture first 2 letters from a product code | Excel Worksheet Functions | |||
Hybrid Mode
