vb to copy and paste based on criteria

[spittingfire]

Hi All,

I have data that starts at A12 in an excel sheet.

What I will like to do is have a vb code that will paste the value of C6 into column P starting at P12.

I will like it to loop based on the data that is in column A.

So for example if I have data in A12, A13, A14 etc then the value of C6 should be pasted into P12, P13, P14 etc.

One last thing I will like to have this in a module where I can call it when needed.

Any help will be appreciated.

Thanks

[SamT]

You don't need VBA.

Use this formula in P12 and copy it down
=IF(A12<>"",$C$6,"")

But, if you insist:
Sub C6_To_P_If_A(WkSht As Worksheet)
Dim Cel As Range
With WkSht
For Each Cel In Range(.Range("A12"), .Cells(Rows.Count, "A").End(xlUp))
If Cel <> "" Then .Cells(Cel.Row, "P") = .Range("C6")
End With
End Sub

[spittingfire]

Thanks SamT.

I am aware of the excel formula however I was looking for a VB solution.

Thanks again for your help

[spittingfire]

Hi SamT I will like to run this in a module but not able to - are you able to modify to allow it to run from a module?

Thanks

[SamT]

It was written from the start to be in a standard Module. That's what you asked for.

For clarity, you probably already know this.
Each Worksheet's Code Page is a Class Module
ThisWorkbook is a Class Module
Each Class Module is a Class Module
Each UserForm is a Class Module
When you Insert a (plain) Module into your Project, it is a "Standard" Module.

[spittingfire]

Yes and what I am trying to do is to use this as a standard module and not as a Class Module. When I create a new module and put the script in and try to run it a dialogue box come up asking for the macro name.

If I change Sub C6_To_P_If_A(WkSht As Worksheet) to Sub C6_To_P_If_A() and run it I then get a compile error. Not sure what I am doing wrong to make it work from a standard module

[Tom Jones]

Try so:



Sub spittingfire_GO ()
call C6_To_P_If_A("Sheet1") 'Change the name of sheet as you need
End Sub



Sub C6_To_P_If_A(WkSht As Worksheet)
Dim Cel As Range
With WkSht
For Each Cel In Range(.Range("A12"), .Cells(Rows.Count, "A").End(xlUp))
If Cel <> "" Then .Cells(Cel.Row, "P") = .Range("C6")
End With
End Sub

[spittingfire]

Thanks Tom Jones

I was able to get help from another forum that did the trick for me




Sub spittingfire()

If Range("A" & Rows.Count).End(xlUp).Row < 13 Then
Range("P12").Value = Range("C6").Value
Else
Range("A12:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Offset(, 15).Value = Range("C6").Value
End If

End Sub

[NoSparks]

spittingfire, please don't embarrass the rest of us Canadians.
Give credit where credit is due.
Then read this (http://www.excelguru.ca/content.php?184).
Thanks

[SamT]

I was able to get help from another forum that did the trick for me



Sub spittingfire()

If Range("A" & Rows.Count).End(xlUp).Row < 13 Then
Range("P12").Value = Range("C6").Value
Else
Range("A12:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Offset(, 15).Value = Range("C6").Value
End If

End Sub


And that sub must be in the particular Worksheet Class Module that it operates on, not a standard Module.

If you took the original code offered, and put it in a Standard Module. you could have put Tom's "spittingfire" sub in any Worksheet's Class Module, (with a slight name change,) and used it.

That is the reason VBA has Standard Modules, so that code can be reused in many places.

Where did you cross post to? Link please.

[spittingfire]

Hey guys I am sorry for all the confusions that I may have caused and maybe I wasn't able to explain myself correctly in what I needed. The above code does what I was looking for. I wanted something that worked in a separate module - let's say module1 that I can call as needed. The script that was provided to me from here worked however as mentioned I was unable to call it the way I needed to. I tried to explain but maybe I could not explain it in a way that made sense to you and I am sorry. I read that link from NoSparks and sorry for the cross posting. It won't happen again. I appreciated all the help that this forum has provided to me in the past and sure I will require more help in the future. Please accept my apology for this.

[SamT]

I wanted something that worked in a separate module - let's say module1 that I can call as needed.


If you took the original code offered, and put it in a Standard Module. you could have put Tom's "spittingfire" sub in any Worksheet's Class Module, (with a slight name change,) and used it.
But, to "Call" a procedure means to Call it from another sub. To "Run" a procedure means to use a menu button or the Macro Menu.

You can Run this sub in a Standard module from the Macro Menu


Sub C6_To_P_If_A()
Dim Cel As Range
With ActiveSheet
If .Range("A" & Rows.Count).End(xlUp).Row < 13 Then
.Range("P12").Value = .Range("C6").Value
Else
For Each Cel In Range(.Range("A12"), .Cells(Rows.Count, "A").End(xlUp))
If Cel <> "" Then .Cells(Cel.Row, "P") = .Range("C6")
Next Cel
End IF
End With
End Sub