Q: How to combine a Cell with a counter value in VBA

[peter_z]

Hey Guys, can anyone see what is wrong with the following code:

               With Worksheets(strSource)
              .Visible = True
 
              lngCounterC = Worksheets("REFERENCE").Range("L:L").Cells.SpecialCells(xlCellTypeConstants).Count
              lngCounterC = lngCounterC - 1
 
              Sheets("Reference").Select
              Range("S8").Copy
              Range("'P7:P' & lngCounterc & ").Select
              Selection.Paste
 
              Worksheets(strSource).Visible = xlSheetVeryHidden
              End With

Error: Method 'Range' of object' _Global failed
Run-time error '1004'

Many thanks in advance

 

[repairman615]

Hello,

maybe change:

Range("'P7:P' & lngCounterc & ").Select

to:

Range("P7:P & lngCounterc & ").Select

 

[peter_z]

Hey there, tried fiddling with the " and ' before but no luck :S

 

[repairman615]

Is that the line that gets highlighted?

try:

Range("P7:P" & lngCounterc).Select

 

[peter_z]

Yup thats the line that gets highlighted, no luck with that formula either... also just realised that this code wouldn't paste into all the cells anyway :S

Do you know any code that would paste it into every individual cell selected?

 

[peter_z]

Trying the following which is allowing me to go through the whole formula... it just isn't displaying any change :S

              With Worksheets(strSource)
              .Visible = True
                
              lngCounterC = Worksheets("REFERENCE").Range("L:L").Cells.SpecialCells(xlCellTypeConstants).Count
              lngCounterC = (lngCounterC - 1)
                
              For i = 1 To lngCounterC
                
              Sheets("Reference").Select
              Range("S8").Copy
              Range("P7:P" & i).Select
              activeSelection.Paste
              
              Next i
              
              Worksheets(strSource).Visible = xlSheetVeryHidden
              End With

 

[repairman615]

What is lngCounterc dimmed as?

You may find a hint as to what happening with this line (place before the line in question)

msgbox lngCounterc

this will show what value the variable has.

this code works for selecting

Range("P7:P" & lngCounterc).Select

The only way I can think of is a for each loop to place the "paste" into each cell.
If we can get the first issue resolved, maybe we can also get the paste.

 

[repairman615]

<font face=Courier New><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("P7:P" & lngCounterc)<br>    cell.Select<br>    Range("S8").Copy<br>    ActiveCell.Paste<br><SPAN style="color:#00007F">Next</SPAN> cell<br></FONT>

 

[peter_z]

The above code takes us back to the error:
Run-time error '1004'
Method 'Range' of object' _Global' failed

Selecting the line:
For Each cell In Range("P7:P" & lngCounterC)

 

[repairman615]

Here is something that may work. This will only mirror the value of the cell S8, in other words, no formats.


<font face=Courier New><SPAN style="color:#00007F">With</SPAN> Worksheets(strSource)<br>    .Visible = <SPAN style="color:#00007F">True</SPAN><br>      <br>    lngCounterc = Worksheets("REFERENCE").Range("L:L").Cells.SpecialCells(xlCellTypeConstants).Count<br>    lngCounterc = (lngCounterc - 1)<br>      <br>      <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> Range("P7:P" & lngCounterc)<br>          cell.Value = Range("S8").Value<br>      <SPAN style="color:#00007F">Next</SPAN> cell<br>    <br>    Worksheets(strSource).Visible = xlSheetVeryHidden<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN></FONT>