Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Limitations
Dear All
With the limitation of the if statement to on 7 variables I need help with a macro to do the following I have twelve columns each representing a particular month of the year with financial data and account numbers along the left of the table. What I want to do is if the month is say 6 (Column "K") then pick up the value in the column for the row pertaining to the account number for the 6th month and return it to say column "A" In the old days the If Statement use to work well but nolonger, can anyone please help with a vb script. Thanks Regards Newman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Limitations
Why not split the IF's over a couple of cells then use =AND(IF1,IF2,IF3,
etc) to get your final answer? "Newman Emanouel" wrote in message ... Dear All With the limitation of the if statement to on 7 variables I need help with a macro to do the following I have twelve columns each representing a particular month of the year with financial data and account numbers along the left of the table. What I want to do is if the month is say 6 (Column "K") then pick up the value in the column for the row pertaining to the account number for the 6th month and return it to say column "A" In the old days the If Statement use to work well but nolonger, can anyone please help with a vb script. Thanks Regards Newman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Limitations
On Oct 9, 11:23 pm, Newman Emanouel wrote:
Dear All With the limitation of the if statement to on 7 variables I need help with a macro to do the following I have twelve columns each representing a particular month of the year with financial data and account numbers along the left of the table. What I want to do is if the month is say 6 (Column "K") then pick up the value in the column for the row pertaining to the account number for the 6th month and return it to say column "A" In the old days the If Statement use to work well but nolonger, can anyone please help with a vb script. Thanks Regards Newman Based on your description, it sounds like you are wanting to perform a two-way table lookup. That can be done using Index/Match/Match in conjunction with each other. Assuming that you account numbers are in E1:E5 and your month number are in row 1 in columns F:K, the formula below would bring back the value from month 6 for Acct4. =INDEX(E1:K5,MATCH("Acct4",E1:E5,0),MATCH(6,E1:K1, 0)) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Limitations
yes I thought of doing that but the lenght of the table is variable and if
there are more formula then it slows the spreadsheet down. What I wanted to do was to do a loop for the then then until the cell is blank. That way i dont have to recalculate anything upto 2000 lines every time. Result ACCNT_TITLE A1_1_BALANCE END_BAL_JAN END_BAL_FEB 995 CASH IN - LOCAL $ 0 0 0 995 ATSE-INV 0 0 0 0 0 EQUITY- CO 4098574.04 98574.04 4094.04 So in column "A" if the period is 2 in Cell "C2" for example pick up the number in Feb on line 2 "CASH IN - LOCAL$" which would be "995" in this case "BIG_j" wrote: Why not split the IF's over a couple of cells then use =AND(IF1,IF2,IF3, etc) to get your final answer? "Newman Emanouel" wrote in message ... Dear All With the limitation of the if statement to on 7 variables I need help with a macro to do the following I have twelve columns each representing a particular month of the year with financial data and account numbers along the left of the table. What I want to do is if the month is say 6 (Column "K") then pick up the value in the column for the row pertaining to the account number for the 6th month and return it to say column "A" In the old days the If Statement use to work well but nolonger, can anyone please help with a vb script. Thanks Regards Newman |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Limitations
Dear All
I managed to work it out and decided to post it for future reference by all Sub proTest() If Sheets("SL Paste TB").Range("F2").Value = 12 Then Columns("E:E").Value = Columns(" X:X").Value Else If Sheets("SL Paste TB").Range("F2").Value = 11 Then Columns("E:E").Value = Columns(" W:W").Value Else If Sheets("SL Paste TB").Range("F2").Value = 10 Then Columns("E:E").Value = Columns(" V:V").Value Else If Sheets("SL Paste TB").Range("F2").Value = 9 Then Columns("E:E").Value = Columns(" U:U").Value Else If Sheets("SL Paste TB").Range("F2").Value = 8 Then Columns("E:E").Value = Columns(" T:T").Value Else If Sheets("SL Paste TB").Range("F2").Value = 7 Then Columns("E:E").Value = Columns(" S:S").Value Else If Sheets("SL Paste TB").Range("F2").Value = 6 Then Columns("E:E").Value = Columns(" R:R").Value Else If Sheets("SL Paste TB").Range("F2").Value = 5 Then Columns("E:E").Value = Columns(" Q:Q").Value Else If Sheets("SL Paste TB").Range("F2").Value = 4 Then Columns("E:E").Value = Columns(" P:P").Value Else If Sheets("SL Paste TB").Range("F2").Value = 3 Then Columns("E:E").Value = Columns(" O:O").Value Else If Sheets("SL Paste TB").Range("F2").Value = 2 Then Columns("E:E").Value = Columns(" N:N").Value Else If Sheets("SL Paste TB").Range("F2").Value = 1 Then Columns("E:E").Value = Columns(" M:M").Value Else MsgBox "No specific information is available about this state." End If End If End If End If End If End If End If End If End If End If End If End If End Sub I would be grateful if there is an easier way to write the code that someone reply to this post, but the key is the legth of the rows in the column is variable. "Newman Emanouel" wrote: Dear All With the limitation of the if statement to on 7 variables I need help with a macro to do the following I have twelve columns each representing a particular month of the year with financial data and account numbers along the left of the table. What I want to do is if the month is say 6 (Column "K") then pick up the value in the column for the row pertaining to the account number for the 6th month and return it to say column "A" In the old days the If Statement use to work well but nolonger, can anyone please help with a vb script. Thanks Regards Newman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Limitations
Following seems a bit simpler -
Sub test1() Dim nVal As Long nVal = Val(Sheets(("SL Paste TB").Range("F2").Value) If nVal = 1 And nVal <= 12 Then Columns("E:E").Value = Columns(nVal + 12).Value Else MsgBox "No specific information is available about this state." End If End Sub Regards, Peter T "Newman Emanouel" wrote in message ... Dear All I managed to work it out and decided to post it for future reference by all Sub proTest() If Sheets("SL Paste TB").Range("F2").Value = 12 Then Columns("E:E").Value = Columns(" X:X").Value Else If Sheets("SL Paste TB").Range("F2").Value = 11 Then Columns("E:E").Value = Columns(" W:W").Value Else If Sheets("SL Paste TB").Range("F2").Value = 10 Then Columns("E:E").Value = Columns(" V:V").Value Else If Sheets("SL Paste TB").Range("F2").Value = 9 Then Columns("E:E").Value = Columns(" U:U").Value Else If Sheets("SL Paste TB").Range("F2").Value = 8 Then Columns("E:E").Value = Columns(" T:T").Value Else If Sheets("SL Paste TB").Range("F2").Value = 7 Then Columns("E:E").Value = Columns(" S:S").Value Else If Sheets("SL Paste TB").Range("F2").Value = 6 Then Columns("E:E").Value = Columns(" R:R").Value Else If Sheets("SL Paste TB").Range("F2").Value = 5 Then Columns("E:E").Value = Columns(" Q:Q").Value Else If Sheets("SL Paste TB").Range("F2").Value = 4 Then Columns("E:E").Value = Columns(" P:P").Value Else If Sheets("SL Paste TB").Range("F2").Value = 3 Then Columns("E:E").Value = Columns(" O:O").Value Else If Sheets("SL Paste TB").Range("F2").Value = 2 Then Columns("E:E").Value = Columns(" N:N").Value Else If Sheets("SL Paste TB").Range("F2").Value = 1 Then Columns("E:E").Value = Columns(" M:M").Value Else MsgBox "No specific information is available about this state." End If End If End If End If End If End If End If End If End If End If End If End If End Sub I would be grateful if there is an easier way to write the code that someone reply to this post, but the key is the legth of the rows in the column is variable. "Newman Emanouel" wrote: Dear All With the limitation of the if statement to on 7 variables I need help with a macro to do the following I have twelve columns each representing a particular month of the year with financial data and account numbers along the left of the table. What I want to do is if the month is say 6 (Column "K") then pick up the value in the column for the row pertaining to the account number for the 6th month and return it to say column "A" In the old days the If Statement use to work well but nolonger, can anyone please help with a vb script. Thanks Regards Newman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement Limitations
Hi
For a VBA solution, that could be achieved more simply as Sub proTest2() Dim lastrow As Long, colno As Long colno = Sheets("SL Paste TB").Range("F2").Value + 12 lastrow = Cells(Rows.Count, colno).End(xlUp).Row Range(Cells(1, 5), Cells(65536, 5)) = "" Range(Cells(1, 5), Cells(lastrow, 5)).Value = Range(Cells(1, colno), Cells(lastrow, colno)).Value End Sub For a non VBA solution, =INDEX(M:X,ROW(),'SL Paste TB'!$F$2) and copy down -- Regards Roger Govier "Newman Emanouel" wrote in message ... Dear All I managed to work it out and decided to post it for future reference by all Sub proTest() If Sheets("SL Paste TB").Range("F2").Value = 12 Then Columns("E:E").Value = Columns(" X:X").Value Else If Sheets("SL Paste TB").Range("F2").Value = 11 Then Columns("E:E").Value = Columns(" W:W").Value Else If Sheets("SL Paste TB").Range("F2").Value = 10 Then Columns("E:E").Value = Columns(" V:V").Value Else If Sheets("SL Paste TB").Range("F2").Value = 9 Then Columns("E:E").Value = Columns(" U:U").Value Else If Sheets("SL Paste TB").Range("F2").Value = 8 Then Columns("E:E").Value = Columns(" T:T").Value Else If Sheets("SL Paste TB").Range("F2").Value = 7 Then Columns("E:E").Value = Columns(" S:S").Value Else If Sheets("SL Paste TB").Range("F2").Value = 6 Then Columns("E:E").Value = Columns(" R:R").Value Else If Sheets("SL Paste TB").Range("F2").Value = 5 Then Columns("E:E").Value = Columns(" Q:Q").Value Else If Sheets("SL Paste TB").Range("F2").Value = 4 Then Columns("E:E").Value = Columns(" P:P").Value Else If Sheets("SL Paste TB").Range("F2").Value = 3 Then Columns("E:E").Value = Columns(" O:O").Value Else If Sheets("SL Paste TB").Range("F2").Value = 2 Then Columns("E:E").Value = Columns(" N:N").Value Else If Sheets("SL Paste TB").Range("F2").Value = 1 Then Columns("E:E").Value = Columns(" M:M").Value Else MsgBox "No specific information is available about this state." End If End If End If End If End If End If End If End If End If End If End If End If End Sub I would be grateful if there is an easier way to write the code that someone reply to this post, but the key is the legth of the rows in the column is variable. "Newman Emanouel" wrote: Dear All With the limitation of the if statement to on 7 variables I need help with a macro to do the following I have twelve columns each representing a particular month of the year with financial data and account numbers along the left of the table. What I want to do is if the month is say 6 (Column "K") then pick up the value in the column for the row pertaining to the account number for the 6th month and return it to say column "A" In the old days the If Statement use to work well but nolonger, can anyone please help with a vb script. Thanks Regards Newman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tab Name Limitations | Excel Discussion (Misc queries) | |||
Limitations on UDF's | Excel Programming | |||
Memory limitations with VBA | Excel Programming | |||
Cell limitations? | Excel Programming | |||
Limitations in UDF:s | Excel Programming |