Run-Time error 422

[olevia]

Hi Forum,

Everytime I run my code I get a Run-time error 424, I believe something is wrong with the variable cl.Row, but I can't fix it. Can anyone help. Thanks in advance. Please code below.

Sub M_snb()
   Sheets("Template").Copy , Sheets(Sheets.Count)
   Sheets(Sheets.Count).Name = "Result"
   Sheets("Template").Visible = False
   With Sheets("Result")
      .Range("B9") = InputBox("Provider's MA Number")
      .Range("B10") = InputBox("Provider's Agency")
      .Range("B11") = InputBox("Provider's Address")
      .Range("K9") = InputBox("Program Specialist")
      .Range("K11") = InputBox("Contact E-Mail")
      .Range("O10") = InputBox("Monitoring Dates")
      For j = 1 To Application.CountIf(Sheets("ACW-Participant").Columns("FE"), "UNMET")
         If j > 1 Then .Range("A1:R45").Copy .Cells(j * 50, 1)
         Set c00 = Application.InputBox("Select the range you want to copy from", Type:=8)
         If Not c00 Is Nothing Then c00.Copy .Range("E15")
         .Range("E12") = Sheets("ACW-Participant").Cells(cl.Row, 5).Value
         .Range("E14") = Sheets("ACW-Participant").Cells(cl.Row, 1).Value
      Next
   End With
End Sub

 

[daverunt]

Hi,

the post is a bit of a mess.
Can you repost it with just the vba code between the code tags.

What is 'cl'? It looks as though you are calling a variable that hasn't been declared / given a value.

 

[Andrew Poulsom]

Welcome to MrExcel.

You haven't assigned an object to cl. What should it be?

 

[olevia]

Hi

I figured out the run-time error but I am still having trouble with the code.

My code is almost running perfect but it's not copying the correct cells. For example: If I want to select "LOVE", "SUNSHINE", and "TOGETHER" from Worksheet ACW cells B4,B6, and B8 and then next loop select "ALWAYS" in ACW Worksheet cell B11 and on next loop select "WONDERFUL" in ACW Worksheet cell B16 after the run of program information on the Template Worksheet is incorrect. The Template Worksheet should have the words "LOVE","SUNSHINE",and "TOGETHER" in Template worksheet cell's E15,E16 and E17 and the word "ALWAYS" by itself only in cell E65 and lastly the word "WONDERFUL" by itself only in cell E115. For some reason the program is adding more information then needed in the Template Worksheet. Please see code below along with example attachment. Thank you in advance. Please see attachment.

Option Explicit
Sub Test1()
   Dim SrcSh, targetSh        As String
   Dim i, x, lastRowsSource   As Integer
   Dim a                      As Long       '<== Counter
   Dim cell                   As Range   '<== Counter
   Dim rngCopyFrom            As Range
   Application.ScreenUpdating = False
   SrcSh = "ACW-Participant"
   lastRowsSource = Sheets(SrcSh).Range("FE" & Rows.Count).End(xlUp).Row
   Sheets("Template").Visible = True
   Sheets("Template").Copy After:=Sheets(SrcSh)
   ActiveSheet.Name = "Result"
   targetSh = ActiveSheet.Name
   Sheets("Template").Visible = False
   i = 1
   x = 0
   Application.ScreenUpdating = True
   Sheets(targetSh).Range("B9") = InputBox("Provider's MA Number")
   Sheets(targetSh).Range("B10") = InputBox("Provider's Agency")
   Sheets(targetSh).Range("B11") = InputBox("Provider's Address")
   Sheets(targetSh).Range("K9") = InputBox("Program Specialist")
   Sheets(targetSh).Range("K11") = InputBox("Contact E-Mail")
   Sheets(targetSh).Range("O10") = InputBox("Monitoring Dates")
   For Each cell In Sheets(SrcSh).Range("FE3:FE" & lastRowsSource)
      If cell = "UNMET" Then
         On Error Resume Next
         Set rngCopyFrom = Application.InputBox("Select the range you want to copy from", Type:=8)
         On Error GoTo 0
         If Not rngCopyFrom Is Nothing Then
            rngCopyFrom.Copy ThisWorkbook.Sheets("Result").Range("E15")
         End If
         If x > 0 Then
            i = i + 50
            Sheets("Result").Range("A1:R45").Copy
            Range("A" & i).PasteSpecial xlPasteAll
         End If
         Sheets(SrcSh).Range("E" & cell.Row).Copy
         Sheets(targetSh).Range("E12").PasteSpecial xlPasteValues
         Sheets(SrcSh).Range("A" & cell.Row).Copy
         Sheets(targetSh).Range("E14").PasteSpecial xlPasteValues
         x = x + 1
         Range("C" & 13 + i) = "Finding # " & x      '<== Finding Counter
         Range("H" & 44 + i) = x                     '<== Finding Counter
      End If
   Next cell
   For a = 45 To x * 50 Step 50    '<== Page Counter
      Range("J" & a) = x         '<== Page Counter
   Next a                          '<== Page Counter
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

I don't see any mention of those words or worksheet or target cells in your code.

 

[RoryA]

olevia
If you are going to cross-post, please have the courtesy to provide links to your posts in other forums.

Also, please stop copy and pasting the same question here as it always seems to end up garbled!

 

[olevia]

Code is not copying correct cells

RoryA and Andrew Poulsom, I appreciate your help and thank you both. Tehe link to the code and attachment is below. I'm just trying to make this code work. It was a project that I started and trying to complete. I have very little knowledge of Macro's and VB but I'm trying, but it seems like I just don't get no help and I try to be as nice as possible because that who I am. Thank you once again, I'm not trying to be a bug a boo I'm just trying to get help.


http://www.excelforum.com/excel-pro...e-is-not-copying-correct-cells.html?p=3174753

 

[olevia]

Re: Code is not copying correct cells

It's funny how everyone is trying to give suggestions but no solution. I guess this is just to hard to figure out.

 

[Andrew Poulsom]

Re: Code is not copying correct cells

You haven't replied to my comment in Post #5.

 

[olevia]

Re: Code is not copying correct cells

That's becuse the post in number #4 showed the changes that were made to solve the run time error but I still have no solution to the question that i ask in Post #4.

My code is almost running perfect but it's not copying the correct cells. For example: If I want to select "LOVE", "SUNSHINE", and "TOGETHER" from Worksheet ACW cells B4,B6, and B8 and then next loop select "ALWAYS" in ACW Worksheet cell B11 and on next loop select "WONDERFUL" in ACW Worksheet cell B16 after the run of program information on the Template Worksheet is incorrect. The Template Worksheet should have the words "LOVE","SUNSHINE",and "TOGETHER" in Template worksheet cell's E15,E16 and E17 and the word "ALWAYS" by itself only in cell E65 and lastly the word "WONDERFUL" by itself only in cell E115. For some reason the program is adding more information then needed in the Template Worksheet. Please see code below along with example attachment.