conditional cell manipulation with caculation in VBA

S

signoreexcel

New Member
Joined
Jun 14, 2013
Messages
7
I would like to use VBA to automate something I do a lot, but my knowledge of VBA is not much, so I would appreciate some help.

What I want to do:

Search column 8 for 0's (only one digit in the cell and it is a zero, not looking for zeros as in 10, 101, 0.01 etc.), and if found then check column 9 to see if it is non-zero. If it is, then change the 0 in column 8 to a 1.

If this change is made, then, in column 10 do the following calculation: log2 of (column9/column8).

I then want to do the same for column 9. That is, search for a lone 0, this time check column 8 to see if it is non-zero, if so, change the 0 in column 9 to a 1, and do the same calculation in column 10: log2 (column9/column8).

Length of all columns will be the same, but this length will be variable and go up to 33,000 rows.

When finished, I would like to select everything in column 10 (they will all be numbers) and change to one decimal place (for example, 9.3, 3948.1, 0.4, 5.4, etc.).
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Assumes your data begin in the first row - change cell references to suit.
Code: 
Sub ChangeZeroToOne()
Dim lR As Long, R As Range, vA As Variant
lR = Cells(Rows.Count, 8).End(xlUp).Row
Set R = Range(Cells(1, 8), Cells(lR, 9))
vA = R.Value
Application.ScreenUpdating = False
For i = LBound(vA, 1) To UBound(vA, 1)
    If vA(i, 1) = 0 And vA(i, 2) <> 0 Then
        vA(i, 1) = 1
        Cells(i, 10).Value = Log(vA(i, 2) / vA(i, 1)) / Log(2)
    End If
Next i
For i = LBound(vA, 2) To UBound(vA, 2)
    If vA(i, 2) = 0 And vA(i, 1) <> 0 Then
        vA(i, 2) = 1
        Cells(i, 10).Value = Log(vA(i, 2) / vA(i, 1)) / Log(2)
    End If
Next i
R.Value = vA
Cells(1, 10).EntireColumn.NumberFormat = "0.0"
End Sub
 
Upvote 0
Thank you very much, Joe, for your code. It will make the change for the first row, but it leaves other rows untouched. Is there a way to have it work on all rows that fit the conditions ?
 
Upvote 0
signoreexcel said:
Thank you very much, Joe, for your code. It will make the change for the first row, but it leaves other rows untouched. Is there a way to have it work on all rows that fit the conditions ?
Click to expand...
Apologies, I should have tested the code more. Try this:
Code: 
Sub ChangeZeroToOne()
Dim lR As Long, R As Range, vA As Variant
lR = Cells(Rows.Count, 8).End(xlUp).Row
Set R = Range(Cells(1, 8), Cells(lR, 9))
vA = R.Value
Application.ScreenUpdating = False
For i = LBound(vA, 1) To UBound(vA, 1)
    If vA(i, 1) = 0 And vA(i, 2) <> 0 Then
        vA(i, 1) = 1
        Cells(i, 10).Value = Log(vA(i, 2) / vA(i, 1)) / Log(2)
    End If
Next i
R.Value = vA
Erase vA
vA = R.Value
For i = LBound(vA, 1) To UBound(vA, 1)
    If vA(i, 2) = 0 And vA(i, 1) <> 0 Then
        vA(i, 2) = 1
        Cells(i, 10).Value = Log(vA(i, 2) / vA(i, 1)) / Log(2)
    End If
Next i
R.Value = vA
Cells(1, 10).EntireColumn.NumberFormat = "0.0"
End Sub
 
Last edited:
Upvote 0
test_id
gene_idgenelocussample_1sample_2statusvalue_1value_2log2(fold_change)test_statp_valueq_valuesignificant
XLOC_000019XLOC_000019AT1G01355Chr1:138512-139568lerNmlerNpNOTEST0.11177813.2011no
XLOC_000040XLOC_000040AT1G01695Chr1:252946-254495lerNmlerNpNOTEST0.05694860#NAME?011no
XLOC_000058XLOC_000058AT1G02065Chr1:365208-367340lerNmlerNpNOTEST0.2317990#NAME?011no
XLOC_000107XLOC_000107AT1G02980Chr1:677868-681310lerNmlerNpNOTEST0.1275530#NAME?
011no
XLOC_000186XLOC_000186AT1G04445Chr1:1207343-1207862lerNmlerNpNOTEST0.4671750#NAME?011no
XLOC_000206XLOC_000206AT1G04778Chr1:1339891-1340327lerNmlerNpNOTEST1.800440#NAME?011no
XLOC_000232XLOC_000232AT1G05510Chr1:1629445-1630863lerNmlerNpNOTEST0.3383870#NAME?011no
XLOC_000242XLOC_000242AT1G05740Chr1:1720673-1721309lerNmlerNpNOTEST0.7358930#NAME?011no

<colgroup><col style="mso-width-source:userset;mso-width-alt:3035; width:62pt" span="2" width="83"> <col style="mso-width-source:userset;mso-width-alt:12544;width:257pt" width="343"> <col style="mso-width-source:userset;mso-width-alt:6436;width:132pt" width="176"> <col style="mso-width-source:userset;mso-width-alt:2194; width:45pt" span="2" width="60"> <col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:2048;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> </colgroup><tbody>
</tbody>
XLOC_032554XLOC_032554-Chr5:21488462-21509609lerNmlerNpNOTEST0.6034780#NAME?011
XLOC_032557XLOC_032557-Chr5:24404952-24405551lerNmlerNpNOTEST1.108010#NAME?011
XLOC_032851XLOC_032851-chloroplast:99938-100098lerNmlerNpOK13.30260#NAME?#NAME?0.09870.998545
XLOC_032864XLOC_032864-mitochondria:3922-12201lerNmlerNpNOTEST0.6481810#NAME?011
XLOC_032919XLOC_032919-mitochondria:153645-154198lerNmlerNpNOTEST0.4702870#NAME?011
XLOC_000116XLOC_000116AT1G03120Chr1:752270-753140lerNmlerNpOK01.80158inf#NAME?0.007850.533667
XLOC_000129XLOC_000129AT1G03320Chr1:803034-819563lerNmlerNpNOTEST00.693607inf011
XLOC_000156XLOC_000156AT1G03880Chr1:985750-988146lerNmlerNpNOTEST00.124255inf011

<colgroup><col style="mso-width-source:userset;mso-width-alt:3035; width:62pt" span="2" width="83"> <col style="mso-width-source:userset;mso-width-alt:12544;width:257pt" width="343"> <col style="mso-width-source:userset;mso-width-alt:6436;width:132pt" width="176"> <col style="mso-width-source:userset;mso-width-alt:2194; width:45pt" span="2" width="60"> <col style="mso-width-source:userset;mso-width-alt:1755;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:2048;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> </colgroup><tbody>
</tbody>

See if this help, or I can try something else.
 
Upvote 0
Can you tell me which columns are #8 and #9 (are these columns H & I)?
Did you try the revised code I posted in Post #4?
 
Upvote 0
Hi Joe,

Sorry I somehow missed the revised code. Revised code works great ! Fantastic ! (Yes, columns 8 and 9 are H and I).

Thank you very much !
 
Upvote 0
signoreexcel said:
Hi Joe,

Sorry I somehow missed the revised code. Revised code works great ! Fantastic ! (Yes, columns 8 and 9 are H and I).

Thank you very much !
Click to expand...
That's great. Glad I could help and thanks for letting me know.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Excel VBA Help
2
Replies
16
Views
470
justincaza
J
J
  • Solved
VBA to calculate and conditional format cell
Replies
6
Views
215
rollis13
rollis13
B
  • Solved
VBA solution for a tally sheet, add 1 to a cell based on current date
2
Replies
10
Views
136
Burrgogi
B
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
215
myall_blues
myall_blues
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
189
awoohaw
awoohaw

Forum statistics

Threads
1,215,429
Messages
6,124,840
Members
449,193
Latest member
MikeVol

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top