Macro Problems

[IREALLYambatman]

Hey guys my code below works only partially.. basically whenever it wants.

For the first part of the code where it looks for dilutions (ie x5 x10 x20 x2.5) it doesn't recognize decimals.. Can the string value be changes to recognize that?

For the C1 part of the code: Seems to never c1 if one of the cells is blank, which it should (which should be a zero so C1 should be written). There are other problems with it too.. I just can't honestly remember what the issues are but rarely does it actually flag all the columns that it should.

Sub AnionsWater()

Sub AnionsWater()


ActiveWorkbook.Worksheets("Edit Here").Cells.EntireColumn.AutoFit


'Find the last used column in a Row: row 1 in this example
  With ActiveSheet
    LastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
  End With
  Range(Range("c10"), Cells(10, LastCol)).Value = "300.1_W"
  Range(Range("c14"), Cells(14, LastCol)).Value = "Water"
  Range(Range("c13"), Cells(13, LastCol)).Value = "1"


Dim cell As Range
Dim loc As Integer
Dim str As String


For Each cell In Range("C4", "ZZ4")
    loc = InStr(1, cell.Value, " x")
    If loc > 0 Then
        str = Mid(cell.Value, loc + 2)
        loc = InStr(1, str, " ")
        If loc > 0 Then
            str = Left(str, loc - 1)
        End If
        If IsNumeric(str) Then cell.Offset(9, 0).Value = CInt(str)
    End If
    
        loc2 = InStr(1, cell.Value, " X")
    If loc2 > 0 Then
        str = Mid(cell.Value, loc2 + 2)
        loc2 = InStr(1, str, " ")
        If loc2 > 0 Then
            str = Left(str, loc2 - 1)
        End If
        If IsNumeric(str) Then cell.Offset(9, 0).Value = CInt(str)
    End If
Next
 
Dim c As Range
For Each c In Range(Range("c13"), Cells(13, LastCol))
    If c.Value * c.Offset(26).Value <= 90 Or c.Value * c.Offset(18).Value >= 115 Then c.Offset(3).Value = "c1"
Next c
End Sub



End Sub

 

[JLGWhiz]

this could be one of the problems.

loc2 = InStr(1, cell.Value, "[COLOR=#FF0000] X[/COLOR]")

It won't find the upper case X when the string is using all lower case.

 

[IREALLYambatman]

I'm confused, I repeated that line with both lower and uppercase.. Also any ideas on how to get it to stop changing values(rounding) like x1.5 to 2?

 

[pvr928]

Hi IREALYambatman

I haven't come across this before, but you are using the function CInt, as in:

If IsNumeric(str) Then cell.Offset(9, 0).Value = CInt(str)

According to www.technonthenet.com, CInt converts a value to an integer: https://www.techonthenet.com/excel/formulas/cint.php

Consequently, I suggest you initially focus on this to troubleshoot.

Cheers

pvr928

 

[JLGWhiz]

Hi IREALYambatman

I haven't come across this before, but you are using the function CInt, as in:

If IsNumeric(str) Then cell.Offset(9, 0).Value = CInt(str)

According to www.technonthenet.com, CInt converts a value to an integer: https://www.techonthenet.com/excel/formulas/cint.php

Consequently, I suggest you initially focus on this to troubleshoot.

Cheers

pvr928

Good catch @pvr928, I had totally missed that. To maintain the decimal values, it would need to be CDbl v. CInt. Or possibly CDec