Checking the Syntax Errors in the Excel Sheet Using Macro

**kollurusk** · 06-04-2012, 09:18 AM

Hi Everybody

i have to write a macro to handle the errors in the 'D1' Sheet when im copy & pasting a new sheet.
Please check the sheet attached.

Please help me in some functions at least how can i handle those errors.

Thanks in Advance
Sunil.k

**arlu1201** · 06-04-2012, 11:12 AM

You need to explain some more. Your question is not clear.

**kollurusk** · 06-05-2012, 02:36 AM

Hi

Please see the macros below examples

1)checking cell value is blank or numeric or string..if its a string im checking there is "Space" or "p" or "D" found in string displaing a error message and changing the color as red color

Sub macro1()

Dim sheet1 As Worksheet

Dim disb1 As Variant

Dim i As Integer

Dim j As Integer

Dim myarray As Variant

i = 1
j = 1
k = 4

myarray = Array("D", " ", "p")

For j = 1 To 8

disb1 = Sheets("sheet1").Cells(k, j).Value

If (IsEmpty(disb1)) Then
MsgBox ("Cell Value is blank: Blank " & disb1)
ElseIf (IsNumeric(disb1)) Then
MsgBox ("Cell Value is Numeric: " & disb1)
Else
For l = 0 To UBound(myarray)
FindChar = myarray
SearchString = Sheets("sheet1").Cells(k, j).Value
For i = 1 To Len(SearchString)
If Mid(SearchString, i, 1) = FindChar(l) Then
Pos = i
MsgBox " Array Value Was Found in the String at Position: " & Pos
Sheets("sheet1").Cells(k, j).Select
Selection.Interior.ColorIndex = 3
MsgBox ("String is : " & SearchString)
End If
Next i
Next
End If
Next

End Sub

Like that i want to handle all the errors in a macro what im commented in excel sheet attached.
Each error handling we can develop as a function.

Thanks
sunil.k

**kollurusk** · 06-05-2012, 02:41 AM

Please check the attached test file

**JapanDave** · 06-05-2012, 02:50 AM

You don't need error handling, you need to fix the syntax errors in your code. what exactly are you trying to do?

**zbor** · 06-05-2012, 04:19 AM

kollurusk,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**royUK** · 06-05-2012, 04:31 AM

Fisrt add code tags to your previous post.

Start using Option Explicit to help detect such errors

Don't add error handling when the problem can be resolved.

Please Login or Register  to view this content.

**kollurusk** · 06-05-2012, 05:31 AM

Hi

Thanks for correcting me...i changed the subject line...
first of all im going to developing the each syntax error as each macro after that i want to develop a complete macro using functions to check all the syntax errors for each cell in excel sheet....correct me may be i am wrong...
please help me how to check the formula contain only these operators and stings they are a "*", "-","/","+", min, max, round, roundup, rounddown only ..this syntax error also i have added in my excel comments...

Ex: round(([disb1]*([Param1]/100)), 2)

**kollurusk** · 06-19-2012, 08:59 AM

in my excel sheet all the cells having the formulas like this how can i declare a array

after calculation the cell value is displayed with value
ex: disb1 = 162 and param1 =100

round(([disb1]*([Param1]/100)),2)

please help me how to declare a array and how to calculate the formula

thanks in advance
sunil.k