writing a vba code for a simple function

[niloy parvaz]

Hi

I have just started using VBA from today and I am slowly getting the grasp of it. For the time being I am supposed to write a code to execute a certain function. Let me break down the scenario. I have a table with some names (which is linked to a different worksheet). if instead of the names, an error message comes up, I want to retrieve all the rows with the errors and transfer these messages to a different worksheet showing the errors.

I was thinking about maybe using strings and conditional commands but since I just started using VBA I cant quite figure out the picture as to how to go about writing the code. Could you please help me out? I would appreciate it

EggHeadCafe - Software Developer Portal of Choice
Silverlight 3 Bing API: A Related Links Widget
http://www.eggheadcafe.com/tutorial...8feb-57559fdf27f2/silverlight-3-bing-api.aspx

 

[Bernie Deitrick]

In the errors are in column A of the activesheet, and you want to transfer the errors to a sheet
named Errors, then you could use


Sub Macro1()
Dim myC As Range
Dim myR As Range
Dim myS As Worksheet
Dim myD As Worksheet

Set myS = ActiveSheet
Set myD = Worksheets("Errors")

myD.Activate

Set myR = Intersect(myS.Range("A:A"), myS.UsedRange)
For Each myC In myR.SpecialCells(xlCellTypeFormulas, 16)
myC.EntireRow.Cut
myD.Cells(Rows.Count, 1).End(xlUp)(2).EntireRow.Select
myD.Paste
Next myC

myR.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[Joel]

Try something like this


Set MyErrors = Sheets(1).Cells.SpecialCells( _
xlCellTypeFormulas, xlErrors).EntireRow
MyErrors.Copy destination:=Sheets(2).Cells