Run-Time error 422

olevia

New Member
Joined
Mar 26, 2013
Messages
8
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.
Code:
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
 
Last edited by a moderator:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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.

Code:
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
 
Last edited by a moderator:
Upvote 0
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! ;)
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top