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.).
 
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
XLOC_000019XLOC_000019AT1G01355Chr1:138512-139568lerNmlerNpNOTESTno
XLOC_000040XLOC_000040AT1G01695Chr1:252946-254495lerNmlerNpNOTESTno
XLOC_000058XLOC_000058AT1G02065Chr1:365208-367340lerNmlerNpNOTESTno
XLOC_000107XLOC_000107AT1G02980Chr1:677868-681310lerNmlerNpNOTESTno
XLOC_000186XLOC_000186AT1G04445Chr1:1207343-1207862lerNmlerNpNOTESTno
XLOC_000206XLOC_000206AT1G04778Chr1:1339891-1340327lerNmlerNpNOTESTno
XLOC_000232XLOC_000232AT1G05510Chr1:1629445-1630863lerNmlerNpNOTESTno
XLOC_000242XLOC_000242AT1G05740Chr1:1720673-1721309lerNmlerNpNOTESTno

<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>
[TD="width: 83"]test_id
[/TD]
[TD="width: 83"]gene_id[/TD]
[TD="width: 343"]gene[/TD]
[TD="width: 176"]locus[/TD]
[TD="width: 60"]sample_1[/TD]
[TD="width: 60"]sample_2[/TD]
[TD="width: 48"]status[/TD]
[TD="width: 77"]value_1[/TD]
[TD="width: 70"]value_2[/TD]
[TD="class: xl63, width: 107"]log2(fold_change)[/TD]
[TD="width: 88"]test_stat[/TD]
[TD="width: 56"]p_value[/TD]
[TD="width: 70"]q_value[/TD]
[TD="width: 66"]significant[/TD]

[TD="align: right"]0.111778[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl63, align: right"]3.2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.0569486[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.231799[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.127553[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: center"]#NAME?
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.467175[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1.80044[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.338387[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.735893[/TD]
[TD="align: right"]0[/TD]
[TD="class: xl63, align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>
XLOC_032557XLOC_032557-Chr5:24404952-24405551lerNmlerNpNOTEST
XLOC_032851XLOC_032851-chloroplast:99938-100098lerNmlerNpOK
XLOC_032864XLOC_032864-mitochondria:3922-12201lerNmlerNpNOTEST
XLOC_032919XLOC_032919-mitochondria:153645-154198lerNmlerNpNOTEST
XLOC_000116XLOC_000116AT1G03120Chr1:752270-753140lerNmlerNpOKinf
XLOC_000129XLOC_000129AT1G03320Chr1:803034-819563lerNmlerNpNOTESTinf
XLOC_000156XLOC_000156AT1G03880Chr1:985750-988146lerNmlerNpNOTESTinf

<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>
[TD="width: 83"]XLOC_032554[/TD]
[TD="width: 83"]XLOC_032554[/TD]
[TD="width: 343"]-[/TD]
[TD="width: 176"]Chr5:21488462-21509609[/TD]
[TD="width: 60"]lerNm[/TD]
[TD="width: 60"]lerNp[/TD]
[TD="width: 48"]NOTEST[/TD]
[TD="width: 77, align: right"]0.603478[/TD]
[TD="width: 70, align: right"]0[/TD]
[TD="width: 107, align: center"]#NAME?[/TD]
[TD="width: 88, align: right"]0[/TD]
[TD="width: 56, align: right"]1[/TD]
[TD="width: 70, align: right"]1[/TD]

[TD="align: right"]1.10801[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]13.3026[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#NAME?[/TD]
[TD="align: center"]#NAME?[/TD]
[TD="align: right"]0.0987[/TD]
[TD="align: right"]0.998545[/TD]

[TD="align: right"]0.648181[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0.470287[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#NAME?[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1.80158[/TD]

[TD="align: center"]#NAME?[/TD]
[TD="align: right"]0.00785[/TD]
[TD="align: right"]0.533667[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0.693607[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0.124255[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</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

C
  • Question Question
Conditional Formatting with multiple criteria for first instance only
Replies
1
Views
98
Cynthtmr
C
R
  • Question Question
VBA to automate calculation
Replies
1
Views
210
HighAndWilder
H
K
  • Question Question
Averageif of cells from three sheets
Replies
7
Views
397
Fluff
Fluff
M
  • Question Question
Cell with 0 value showing blank
2
Replies
15
Views
1K
mhessnm
M
I
  • Solved
Check condition of cell value before populating combobo list
Replies
2
Views
291
ipbr21054
I

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