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

 

[peter_z]

still the same error as above unfortunately.

 

[repairman615]

You know if this gets solved, it will be that much better now... I mean, if it were easy, victory wouldn't be as sweet!

what does this msgbox say when you run:

<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>      MsgBox lngCounterc<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>

 

[peter_z]

Hey mate, hope you had a good weekend.
Coming back to this problem first thing Monday morning with a fresh set of eyes I've realised I have been referencing the cell on the wrong sheet!!!

Woops... but still the code is currently entering through perfectly now but not copying over the required cell.

Using:

              With Worksheets(strWorksheet)
                
              lngCounterC = Worksheets("STATEMENT").Range("L:L").Cells.SpecialCells(xlCellTypeConstants).Count
              lngCounterC = (lngCounterC - 1)
                             
             'For i = 1 To lngCounterC
             'Sheets("Reference").Select
             'Range("S8").Copy
             'Sheets("STATEMENT").Range("P7:P" & i).Select
             'Selection.Paste
             'Next i
      
              For Each cell In Range("P7:P" & lngCounterC)
              cell.Value = Worksheets("Reference").Range("S8").Value
              Next cell
              End With

 

[repairman615]

Hello,

Glad you got the "sheet reference" worked out.

What is the required cell?
Is it missing a cell and is it copying like it should?

Please explain a little further.

Almost there,
Jeff

 

[peter_z]

The cell required is a list cell.
It isn't copying into the specified cells.

However when I make a selection in the list box it is correctly copying the value.

Googling an alternative for the .value code which fingers crossed will make it work.

 

[peter_z]

My Googling tells me I'm gonna need to figure out how to create a list box in VBA. Thanks heaps for your help on this one Jeff, much appreciated!

 

[repairman615]

I am lost but but here is a link for creating listbox:

http://www.mrexcel.com/archive/VBA/5987.html

 

[peter_z]

If we can get this working it would be awesome:

              With Worksheets(strWorksheet)
                
              lngCounterC = Worksheets(strWorksheet).UsedRange.Row + Worksheets(strWorksheet).UsedRange.Rows.Count
      
              For Each cell In Range("P7:P" & lngCounterC - 1)
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
              xlBetween, Formula1:="30"
              'cell.Value = Worksheets("Reference").Range("S8").Value
              Next cell
              End With

Current error message: Object doesn't support this property or method
Run time error 438

Cheers Jeff

 

[peter_z]

Finally got this thing working lol...

Here's the code:

              With Worksheets(strWorksheet)
                
              lngCounterC = Worksheets(strWorksheet).UsedRange.Row + Worksheets(strWorksheet).UsedRange.Rows.Count
      
              Range("P7:P" & lngCounterC - 1).Select
                With Selection.Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:="30"
                End With
                
              End With

Thanks again for your help, much appreciated!