Solved

conditional cell changes and calculation

Posted on 2013-11-13
11
171 Views
Last Modified: 2013-12-02
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
Comment
Question by:vbaabv
  • 6
  • 5
11 Comments
 
LVL 81

Expert Comment

by:byundt
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

Open in new window

0
 

Author Comment

by:vbaabv
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

by:byundt
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.

Brad
Log2Q28293694.xlsm
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:vbaabv
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.

Thanks for your help.
0
 
LVL 81

Expert Comment

by:byundt
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

by:byundt
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

by:vbaabv
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

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

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
 

Author Comment

by:vbaabv
ID: 39687518
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
 

Author Comment

by:vbaabv
ID: 39690783
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
 

Author Closing Comment

by:vbaabv
ID: 39690785
Nice job.  Thank you.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question