can a string be too long?

[melliot2]

Dim ph as String
ph= "PH (LC)"
If InStr(Cells(1, c), ph) Then ph = Cells(1, c)

I am trying to use formula. It works great when the contains cell is short. as in one line in the formula bar. However sometimes it will be much longer up to four lines in the formula bar. Would the lenghth cause it to not set the ph variable to the entire cell contents?

 

[Hermanito]

I might be wrong but I thought a cell can hold up to 32k characters (although it will only show the first 1024!), while a string is capable to hold much more, so I don't think you will have issues with putting entire cell contents in a string variable...

 

[melliot2]

I thought that was the case. But now that doesn't explain why this equations isn't working on the longer strings. hmmm

 

[Hermanito]

you are relying on the default property of Cells, I doubt if it has any influence, but can you try this anyway, just to be sure?

If InStr(Cells(1, c).Value, ph) Then ph = Cells(1, c).Value

 

[melliot2]

Unfortunatly that did not work. Thank you!

 

[shg]

Your code works fine for me.

The default property of a range is Value; the Text property only returns the first 1024 characters.

As written, your test is case-sensitive; is that what you require?

 

[Hermanito]

exactly how long is the contents of this cell?
Can you try to add the Debug.Print line and reply with the result?

Dim ph as String
ph= "PH (LC)"
Debug.Print Len(Cells(1,c).Value)
If InStr(Cells(1, c).Value, ph) Then ph = Cells(1, c).Value

 

[melliot2]

Is there a way to make it not case sensitive. I am not sure that this is the problem but I have tried the debug step and it returned nothing.
I am starting to pull at straws.

Your code works fine for me.

The default property of a range is Value; the Text property only returns the first 1024 characters.

As written, your test is case-sensitive; is that what you require?

 

[shg]

Dim ph as String
 
ph= "PH (LC)"
If InStr(Cells(1, c).Value, ph, vbTextCompare) Then ph = Cells(1, c).Value

 

[melliot2]

I have discovered that this isn't where my problem lies. The large print below is where it lies. It just so happens that is reproducable error on the ph variable. Thank you for you help.

Dim MyCols
Dim a As Long, FC As Long
Dim AP As String
Dim approval As String
Dim dex As String
Dim dp1 As String
Dim dp2 As String
Dim ds As String
Dim fx As String
Dim hc As String
Dim hs As String
Dim i2 As String
Dim iva As String
Dim malt As String
Dim ph As String
Dim temp As String
Dim tri As String
Dim bac As String
Dim debris As String
Dim mold As String
Dim yeast As String
Dim acetalde As String
Dim comments As String
Dim icumsa As String
Dim ferm As String
Dim resist As String
Dim sodium As String
Dim ph10 As String
Dim lovi As String
Dim trans350 As String
Dim trans390 As String
Dim trans395 As String
Dim c As Integer
Dim r As Integer


'Product
Sheets("Product").Select
AP = "2AP (LASTRESULT)(Int Specs:-0.5)"
approval = "APPROVAL (OPERATION )(Int Specs:1-1)"
dex = "DEX (LS-030-1 )(Int Specs:45-55)"
dp1 = "DP1 (LS-030-1 )(Int Specs:93-)"
dp2 = "DP2-PLUS (LS-030-1 )(Int Specs:2-6.8)"
ds = "DS (LS-002-1 )"
fx = "FX (LS-030-1 )"
hc = "HC (LS-008-1 )(Int Specs:-1.5)"
hs = "HS (LS-030-1 )(Int Specs)"
i2 = "I2 (LS-013-1 )(Int Specs:1-1)"
iva = "IVA (LASTRESULT)(Int Specs:-5)"
malt = "MALT (LS-030-1 )(Int Specs)"
ph = "PH (LS-005-1 )"
temp = "TEMP (PROCESS )(Int Specs:85-105)"
tri = "TRI (LS-030-1 )(Int Specs)"
bac = "BACTERIA (MS-003-1 )(Int Specs:-300)"
debris = "DEBRIS (MS-003-1 )(Int Specs)"
mold = "MOLD (MS-003-1 )"
yeast = "YEAST (MS-003-1 )"
acetalde = "ACETALDE (LS-053-1 )(Int Specs:-60)"
comments = "COMMENTS (OPERATION )(Int Specs:5-35)"

c = 7
Do
c = c + 1
If InStr(Cells(1, c), AP) Then AP = Cells(1, c) Else
If InStr(Cells(1, c), approval) Then approval = Cells(1, c) Else
If InStr(Cells(1, c), dex) Then dex = Cells(1, c) Else
If InStr(Cells(1, c), dp1) Then dp1 = Cells(1, c) Else
If InStr(Cells(1, c), dp2) Then dp2 = Cells(1, c) Else
If InStr(Cells(1, c), ds) Then ds = Cells(1, c) Else
If InStr(Cells(1, c), fx) Then fx = Cells(1, c) Else
If InStr(Cells(1, c), hc) Then hc = Cells(1, c) Else
If InStr(Cells(1, c), hs) Then hs = Cells(1, c) Else
If InStr(Cells(1, c), i2) Then i2 = Cells(1, c) Else
If InStr(Cells(1, c), iva) Then iva = Cells(1, c) Else
If InStr(Cells(1, c), malt) Then malt = Cells(1, c) Else
If InStr(Cells(1, c), ph) Then ph = Cells(1, c) Else
If InStr(Cells(1, c), temp) Then temp = Cells(1, c) Else
If InStr(Cells(1, c), tri) Then tri = Cells(1, c) Else
If InStr(Cells(1, c), bac) Then bac = Cells(1, c) Else
If InStr(Cells(1, c), debris) Then debris = Cells(1, c) Else
If InStr(Cells(1, c), mold) Then mold = Cells(1, c) Else
If InStr(Cells(1, c), yeast) Then yeast = Cells(1, c) Else
If InStr(Cells(1, c), acetalde) Then acetalde = Cells(1, c) Else
If InStr(Cells(1, c), comments) Then comments = Cells(1, c)
Loop Until c = 29

Application.ScreenUpdating = False
MyCols = Array("Process_datetime", "Carrier Info", "Customer", "Product", "Load Order#", "Weight", "Lot Number", _
AP, approval, dex, dp1, dp2, ds, fx, hc, hs, i2, iva, malt, ph, temp, tri, bac, debris, mold, yeast, _
acetalde, comments)

For a = LBound(MyCols) To UBound(MyCols)
FC = 0
'On Error Resume Next

FC = Application.Match(MyCols(a), Rows(1), 0)
'On Error GoTo 0
If FC = 0 Then
Columns(a).Insert
Cells(1, a).Value = MyCols(a)
ElseIf a = FC Then
'do nothing
Else
Columns(20).Insert
Columns(FC + 1).Cut Destination:=Columns(a)
End If
Next a
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True