If Statement Limitations

[Guest]

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

 

[BIG_j]

Why not split the IF's over a couple of cells then use =AND(IF1,IF2,IF3,
etc) to get your final answer?

 

[JW]

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))

 

[Guest]

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

 

[Guest]

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").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.

 

[Peter T]

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

 

[Roger Govier]

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