Check if cell contains !

T

ThomasOES

Board Regular
Joined
Aug 29, 2017
Messages
174
I'm submitting a chemistry report but some of the elements are listed in % and some in ppm. I want to change all elements to %. The elements reported in ppm have an exclamation point in the cell containing the element symbol. I would like vba to copy each column and place the copied data two rows below. In the cases where an element is ppm, Multiply the data by 0.0001 and place data two rows below. I've pasted some sample data below. The Boron(B), Calcium(Ca), and Magnesium(Mg) are all reported by a spectrometer in ppm. I want to change the report to %. I would place the data beginning at cell A100. Top row would end at cell L100. Something like, Range("A100:L100").Select. If any cells have "!" Then cell End(xlDown)*0.0001.

Make This

[TABLE="width: 768"]
<colgroup><col width="64" style="width:48pt" span="12"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Sample[/TD]
[TD="width: 64, bgcolor: transparent"]Al[/TD]
[TD="width: 64, bgcolor: transparent"]As[/TD]
[TD="width: 64, bgcolor: transparent"]B![/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]Ca![/TD]
[TD="width: 64, bgcolor: transparent"]Co[/TD]
[TD="width: 64, bgcolor: transparent"]Cr[/TD]
[TD="width: 64, bgcolor: transparent"]Cu[/TD]
[TD="width: 64, bgcolor: transparent"]Mg!*[/TD]
[TD="width: 64, bgcolor: transparent"]Mn[/TD]
[TD="width: 64, bgcolor: transparent"]Mo[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-1[/TD]
[TD="bgcolor: transparent"]0.0004[/TD]
[TD="bgcolor: transparent"]0.005[/TD]
[TD="bgcolor: transparent"]2.96[/TD]
[TD="bgcolor: transparent"]0.369[/TD]
[TD="bgcolor: transparent"]14.9[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.244[/TD]
[TD="bgcolor: transparent"]2.05[/TD]
[TD="bgcolor: transparent"]0.76[/TD]
[TD="bgcolor: transparent"]0.0464[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-2/B-1[/TD]
[TD="bgcolor: transparent"]0.0008[/TD]
[TD="bgcolor: transparent"]0.0046[/TD]
[TD="bgcolor: transparent"]2.72[/TD]
[TD="bgcolor: transparent"]0.358[/TD]
[TD="bgcolor: transparent"]17.31[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.24[/TD]
[TD="bgcolor: #000000"]3.41[/TD]
[TD="bgcolor: transparent"]0.77[/TD]
[TD="bgcolor: transparent"]0.0457[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B-2[/TD]
[TD="bgcolor: transparent"]0.0008[/TD]
[TD="bgcolor: transparent"]0.0041[/TD]
[TD="bgcolor: transparent"]2.78[/TD]
[TD="bgcolor: transparent"]0.358[/TD]
[TD="bgcolor: transparent"]17.51[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]2.45[/TD]
[TD="bgcolor: transparent"]0.768[/TD]
[TD="bgcolor: transparent"]0.0456[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C-1[/TD]
[TD="bgcolor: transparent"]0.0009[/TD]
[TD="bgcolor: transparent"]0.0043[/TD]
[TD="bgcolor: transparent"]2.43[/TD]
[TD="bgcolor: transparent"]0.357[/TD]
[TD="bgcolor: transparent"]16.53[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.243[/TD]
[TD="bgcolor: transparent"]2.18[/TD]
[TD="bgcolor: transparent"]0.756[/TD]
[TD="bgcolor: transparent"]0.0458[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C-2/D-1[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0043[/TD]
[TD="bgcolor: transparent"]2.52[/TD]
[TD="bgcolor: transparent"]0.358[/TD]
[TD="bgcolor: transparent"]18.46[/TD]
[TD="bgcolor: transparent"]0.0077[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.243[/TD]
[TD="bgcolor: transparent"]2.46[/TD]
[TD="bgcolor: transparent"]0.759[/TD]
[TD="bgcolor: transparent"]0.0458[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D-2[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0051[/TD]
[TD="bgcolor: transparent"]2.84[/TD]
[TD="bgcolor: transparent"]0.367[/TD]
[TD="bgcolor: transparent"]15.03[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]2.47[/TD]
[TD="bgcolor: #C8C8C8"]0.773[/TD]
[TD="bgcolor: transparent"]0.0464[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E-1[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0043[/TD]
[TD="bgcolor: transparent"]2.75[/TD]
[TD="bgcolor: transparent"]0.361[/TD]
[TD="bgcolor: transparent"]15.76[/TD]
[TD="bgcolor: transparent"]0.0078[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]2.33[/TD]
[TD="bgcolor: transparent"]0.765[/TD]
[TD="bgcolor: transparent"]0.0461[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E-2/F-1[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0048[/TD]
[TD="bgcolor: transparent"]2.75[/TD]
[TD="bgcolor: transparent"]0.355[/TD]
[TD="bgcolor: transparent"]17.35[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.241[/TD]
[TD="bgcolor: transparent"]2.22[/TD]
[TD="bgcolor: #C8C8C8"]0.755[/TD]
[TD="bgcolor: transparent"]0.0456[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F-2[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0046[/TD]
[TD="bgcolor: transparent"]2.77[/TD]
[TD="bgcolor: transparent"]0.366[/TD]
[TD="bgcolor: transparent"]15.98[/TD]
[TD="bgcolor: transparent"]0.0073[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]2.27[/TD]
[TD="bgcolor: transparent"]0.771[/TD]
[TD="bgcolor: transparent"]0.0458[/TD]
[/TR]
</tbody>[/TABLE]

Look Like This

[TABLE="width: 768"]
<colgroup><col width="64" style="width:48pt" span="12"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Sample[/TD]
[TD="width: 64, bgcolor: transparent"]Al[/TD]
[TD="width: 64, bgcolor: transparent"]As[/TD]
[TD="width: 64, bgcolor: transparent"]B![/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"]Ca![/TD]
[TD="width: 64, bgcolor: transparent"]Co[/TD]
[TD="width: 64, bgcolor: transparent"]Cr[/TD]
[TD="width: 64, bgcolor: transparent"]Cu[/TD]
[TD="width: 64, bgcolor: transparent"]Mg!*[/TD]
[TD="width: 64, bgcolor: transparent"]Mn[/TD]
[TD="width: 64, bgcolor: transparent"]Mo[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-1[/TD]
[TD="bgcolor: transparent"]0.0004[/TD]
[TD="bgcolor: transparent"]0.005[/TD]
[TD="bgcolor: transparent"]0.00030[/TD]
[TD="bgcolor: transparent"]0.369[/TD]
[TD="bgcolor: transparent"]0.00149[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.244[/TD]
[TD="bgcolor: transparent"]0.00205[/TD]
[TD="bgcolor: transparent"]0.76[/TD]
[TD="bgcolor: transparent"]0.0464[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A-2/B-1[/TD]
[TD="bgcolor: transparent"]0.0008[/TD]
[TD="bgcolor: transparent"]0.0046[/TD]
[TD="bgcolor: transparent"]0.00027[/TD]
[TD="bgcolor: transparent"]0.358[/TD]
[TD="bgcolor: transparent"]0.00173[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.24[/TD]
[TD="bgcolor: #000000"]0.00341[/TD]
[TD="bgcolor: transparent"]0.77[/TD]
[TD="bgcolor: transparent"]0.0457[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B-2[/TD]
[TD="bgcolor: transparent"]0.0008[/TD]
[TD="bgcolor: transparent"]0.0041[/TD]
[TD="bgcolor: transparent"]0.00028[/TD]
[TD="bgcolor: transparent"]0.358[/TD]
[TD="bgcolor: transparent"]0.00175[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]0.00245[/TD]
[TD="bgcolor: transparent"]0.768[/TD]
[TD="bgcolor: transparent"]0.0456[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C-1[/TD]
[TD="bgcolor: transparent"]0.0009[/TD]
[TD="bgcolor: transparent"]0.0043[/TD]
[TD="bgcolor: transparent"]0.00024[/TD]
[TD="bgcolor: transparent"]0.357[/TD]
[TD="bgcolor: transparent"]0.00165[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.243[/TD]
[TD="bgcolor: transparent"]0.00218[/TD]
[TD="bgcolor: transparent"]0.756[/TD]
[TD="bgcolor: transparent"]0.0458[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C-2/D-1[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0043[/TD]
[TD="bgcolor: transparent"]0.00025[/TD]
[TD="bgcolor: transparent"]0.358[/TD]
[TD="bgcolor: transparent"]0.00185[/TD]
[TD="bgcolor: transparent"]0.0077[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.243[/TD]
[TD="bgcolor: transparent"]0.00246[/TD]
[TD="bgcolor: transparent"]0.759[/TD]
[TD="bgcolor: transparent"]0.0458[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D-2[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0051[/TD]
[TD="bgcolor: transparent"]0.00028[/TD]
[TD="bgcolor: transparent"]0.367[/TD]
[TD="bgcolor: transparent"]0.00150[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]0.00247[/TD]
[TD="bgcolor: #C8C8C8"]0.773[/TD]
[TD="bgcolor: transparent"]0.0464[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E-1[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0043[/TD]
[TD="bgcolor: transparent"]0.00028[/TD]
[TD="bgcolor: transparent"]0.361[/TD]
[TD="bgcolor: transparent"]0.00158[/TD]
[TD="bgcolor: transparent"]0.0078[/TD]
[TD="bgcolor: transparent"]0.155[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]0.00233[/TD]
[TD="bgcolor: transparent"]0.765[/TD]
[TD="bgcolor: transparent"]0.0461[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E-2/F-1[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0048[/TD]
[TD="bgcolor: transparent"]0.00028[/TD]
[TD="bgcolor: transparent"]0.355[/TD]
[TD="bgcolor: transparent"]0.00174[/TD]
[TD="bgcolor: transparent"]0.0075[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.241[/TD]
[TD="bgcolor: transparent"]0.00222[/TD]
[TD="bgcolor: #C8C8C8"]0.755[/TD]
[TD="bgcolor: transparent"]0.0456[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F-2[/TD]
[TD="bgcolor: transparent"]0.0006[/TD]
[TD="bgcolor: transparent"]0.0046[/TD]
[TD="bgcolor: transparent"]0.00028[/TD]
[TD="bgcolor: transparent"]0.366[/TD]
[TD="bgcolor: transparent"]0.00160[/TD]
[TD="bgcolor: transparent"]0.0073[/TD]
[TD="bgcolor: transparent"]0.154[/TD]
[TD="bgcolor: transparent"]0.242[/TD]
[TD="bgcolor: transparent"]0.00227[/TD]
[TD="bgcolor: transparent"]0.771[/TD]
[TD="bgcolor: transparent"]0.0458[/TD]
[/TR]
</tbody>[/TABLE]

Sometimes the exclamation isn't the right-most character
Round to 5 digits

Any help is greatly appreciated
Tom
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
You will need to determine how you want to trigger the event (I used the double-click event), but this should work.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim theLastRow As Integer
Dim dataValue As Variant

theCopiedData = Range("A1:L10").Copy
Range("A13").PasteSpecial

For Each dataValue In Range("J14:J22")
Range("J" & dataValue.Row) = dataValue * 0.0001
Next
Range("A1").Select
Application.CutCopyMode = False
End Sub
 
Upvote 0
Try:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG11Apr09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rngac [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] Range, Mpy [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A100"), Range("A" & Rows.Count).End(xlUp))
Rng.Resize(, 12).Copy Rng.Offset(Rng.Count + 2)
[COLOR="Navy"]Set[/COLOR] Rngac = Rng(1).Offset(Rng.Count + 2).Resize(, 12)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Rngac
 [COLOR="Navy"]If[/COLOR] InStr(Ac, "!") > 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Mpy = Ac.Offset(1).Resize(Rng.Count - 1)
        Mpy.Value = Application.Round(Evaluate(Mpy.Address & "*0.0001"), 5)
 [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick
I'm busy doing other analysis, but I need to mention the sample data I posted will vary. Sometimes different elements or different number of elements. Also variable number of samples. Can the code have flexibility to accommodate changing numbers of rows and columns?

Thanks
Tom
 
Upvote 0
Try this:-
This code should take care of varying numbers of columns and rows, and paste the copied version 2 rows after the end of the data starting "A100".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG12Apr32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rngac [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] Range, Mpy [COLOR="Navy"]As[/COLOR] Range
Lst = Cells("100", Columns.Count).End(xlToLeft).Column
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A100"), Range("A" & Rows.Count).End(xlUp))
Rng.Resize(, Lst).Copy Rng.Offset(Rng.Count + 2)
[COLOR="Navy"]Set[/COLOR] Rngac = Rng(1).Offset(Rng.Count + 2).Resize(, 12)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Ac [COLOR="Navy"]In[/COLOR] Rngac
 [COLOR="Navy"]If[/COLOR] InStr(Ac, "!") > 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Mpy = Ac.Offset(1).Resize(Rng.Count - 1)
        Mpy.Value = Application.Round(Evaluate(Mpy.Address & "*0.0001"), 5)
 [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick
Works perfectly. Your code allows me to finish a huge project. Cant thank you enough.
Tom
 
Upvote 0
Hello Mick
I made an alteration to your code
Code: 
[COLOR=#000080]Set[/COLOR] Rngac = Rng(1).Offset(Rng.Count + 2).Resize(, 12)
I believe the Resize property needs to match the number of columns
I changed your code to
Code: 
[COLOR=#000080]Set[/COLOR] Rngac = Rng(1).Offset(Rng.Count + 2).Resize(, Lst)
After my edit, I stepped through the code and it would cycle the same number of columns. Before the edit it would cycle 12 times. I hope I'm correct that the edit affects the number of columns.
Thanks
Tom
 
Upvote 0
Yes !!, sorry about that I failed to update "12" with "lst" in all the places.
Hope its working for OK now !!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

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