# conditional cell changes and calculation

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.
###### Who is Participating?

Commented:
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

Commented:
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

Research ScientistAuthor Commented:
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

Commented:
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

Research ScientistAuthor Commented:
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

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

Commented:
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

Research ScientistAuthor Commented:
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

Research ScientistAuthor Commented:

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

Research ScientistAuthor Commented:

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

Research ScientistAuthor Commented:
Nice job.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.