Solved

# conditional cell changes and calculation

Posted on 2013-11-13
179 Views
I have an Excel worksheet in which I want to begin by checking column 7 (G) for the presence of a 0 (zero). If it is a zero, change it to a 1 and then in column 9 (J) calculate the log base 2 ratio of column 8 (H) to column 7 (G).

Then, check column 8 (H) for the presence of a zero. If it is a zero change it to a 1, and calculate the log base 2 ratio of column 8 (H) to column 7 (G).

Then do this for every 12 columns; so the next columns to check would be column 20 (T) and column 21 (U) for the presence of 0's and calculate the log base 2 in column V (21). Then columns 32 and 33 are checked for 0's, and the log base 2 calculated in column 34, etc, until the end of the worksheet which can have a variable amount of columns.
0
Question by:vbaabv
• 6
• 5

LVL 81

Expert Comment

ID: 39646914
Here is a macro that will calculate the log base 2 of your ratios. It wasn't clear in your question whether the formula goes only where a value was changed from 0 to 1, or in every row. I show both alternatives, but commented out the "every row" alternative.
``````Sub LogRatio()
Dim cel As Range, rg As Range
Dim i As Long, j As Long, jStart As Long, k As Long, nRows As Long, nCols As Long
With ActiveSheet
Set rg = .UsedRange
Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1)      'First row contains header labels
nCols = rg.Columns.Count
jStart = .Range("G2").Column - (rg.Column - 1)
For j = jStart To nCols Step 13
On Error Resume Next
For k = 1 To 2
Do
Set cel = Nothing
Set cel = rg.Columns(j + k - 1).Find(0, LookAt:=xlWhole, LookIn:=xlValues)
If cel Is Nothing Then Exit Do
cel.Value = 1
cel.Offset(0, 3 - k).FormulaR1C1 = "=LN(RC[-1]/RC[-2])/LN(2)"   'Use this statement if formula only in rows where 0 changed to 1
Loop
Next
'rg.Columns(j).Offset(0, 2).FormulaR1C1 = "=LN(RC[-1]/RC[-2])/LN(2)"     'Use this statement if formula needed in every row
Next
End With
End Sub
``````
0

Author Comment

ID: 39686013
Hi byundt,

Sorry, I thought I had responded to your script. I ran it a couple of weeks ago, but apparently I did not write a reply. Sorry for that.

You script does very well, except it only works on columns G, H and I. It needs to also do the same for columns S, T and I, then AE, AF, and AG, (every 12 columns) etc. until the end of the worksheet (where the last information is located, which will be 6 columns to the right of the first of the last three columns to be worked on.)

The log(2) calculation needs to be done only when there is a change of 0 to 1 in that row.
If there is a zero in two adjacent columns, then I guess when the log(2) calculation is done the second time (after the second 0 is changed to a 1, the end result will be correct.  Log(2) will = 0.
0

LVL 81

Expert Comment

ID: 39686028
The code is working in my test workbook. Please try it on the attached sample workbook.

If it is not working on your real workbook, please post a sanitized version so I can identify and fix the problem.

Log2Q28293694.xlsm
0

Author Comment

ID: 39686212
I tried it again on my worksheet (there are over six thousand rows, so it took a while) and it still does not seem to go beyond the first three columns, G, H and I.

I have attached a test worksheet which I have pared down to 50 rows and have highlighted the cells in red that contain zeros that need to be changed to 1. You can also see, that the log(2) column, the first one being column I, the next one is column AF, etc., presently have either a '0', an 'inf', or a '#NAME?' wherever a log(2) calculation has to be made.

0

LVL 81

Expert Comment

ID: 39686401
No file was attached. Remember that you need to enter a description after attaching the file to make it stick.
0

LVL 81

Expert Comment

ID: 39686405
I should also point out my understanding of your request:
If there is a 0 in either column G or H, turn it into a 1 and put the log(2) formula in column I.
Repeat with columns T and U, putting the formula in column V.
Repeat every block of 12 columns until the end of your data.
0

Author Comment

ID: 39687505
Sorry, again. I will have to wait until Monday before I can get access to the file I made.

Thank you again for your efforts.

Yes, you do understand correctly.

After thinking about this, I think that your script may in fact be working correctly; the problem being that the first set of three columns should be H, I and J with the log2 calculation in J. Then the next set of columns being T, U and V, and so on, every 12. This makes sense to me because T, U, and V are 12 columns away from H, I  and J and not from G, H, and I.  I think I mistakenly put columns G, H and I in my request, but I will have to wait until Monday to check it. So, your script could very well be working correctly, but I did not see it because I was looking at different columns. However, it will be Monday before I can check. If my suspicion is correct, I can probably tweak the script to work on the columns I need. In either case, I will notify you by early Monday afternoon.
0

LVL 81

Accepted Solution

byundt earned 500 total points
ID: 39687511
You'll be wanting to play with statements 8 and 9 in my code:
``````    jStart = .Range("G2").Column - (rg.Column - 1)
For j = jStart To nCols Step 13
``````

The reference to cell G2 should be to the first cell that gets checked for a 0. Presumably, you'll change it to refer to cell H2.

The reference to Step 13 should be to the number of columns in each repeating block. Presumably, you'll change it to 12 so you increment from column H to column T.

0

Author Comment

ID: 39687518

Thank you very much for the help. The more I think of it, the more my suspicion is that your code works and I made a mistake in the original request. However, I don't have one of those worksheets home with me and I just want to make sure. I will try it out Monday morning and reply I soon as I can.
0

Author Comment

ID: 39690783

My suspicion was wrong and the first column is column G. So, the first three columns to work on are G, H and the log2 calculation in column I.

However, I just changed the '13' to '12' in the following line:

For j = jStart To nCols Step 13

and everything works fine !

The mistake was in my original request, I stated that the next set of three columns would be T, U and V, but it should have been S, T and U which are 12 columns away from G, H and I.

Great job !  Thank you very much,

Matthew
0

Author Closing Comment

ID: 39690785
Nice job.  Thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overâ€¦