[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

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.
0
vbaabv
Asked:
vbaabv
  • 6
  • 5
1 Solution
 
byundtCommented:
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

Open in new window

0
 
vbaabvResearch 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
 
byundtCommented:
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.

Brad
Log2Q28293694.xlsm
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
vbaabvResearch 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.

Thanks for your help.
0
 
byundtCommented:
No file was attached. Remember that you need to enter a description after attaching the file to make it stick.
0
 
byundtCommented:
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
 
vbaabvResearch 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
 
byundtCommented:
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

Open in new window


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.

Brad
0
 
vbaabvResearch ScientistAuthor Commented:
Brad,

  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
 
vbaabvResearch ScientistAuthor Commented:
Hi Brad,

  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
 
vbaabvResearch ScientistAuthor Commented:
Nice job.  Thank you.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now