My code fails

[kirkm]

Hi,

I'm attemptng the following, to put mVal into any mCell in mSheet


Sub test(mSheet, mVal, mCell)
Dim wk As Workbook
Set wk = ActiveWorkbook
Dim sht As Worksheet
Set sht = wk.Worksheets(mSheet)

With sht
Range(mCell).Select
ActiveCell.Value = mVal
End With
Set sht = Nothing
Set wk = Nothing
End Sub



It doesn't work, but also doesn't report any errors.

It there anything obviously wrong with it?


Thanks - Kirk

 

[Gary Keramidas]

this works for me, but you should dim the variables: mcell, msheet, mval:

Sub test()
Dim wk As Workbook
Set wk = ActiveWorkbook
mSheet = "Sheet1"
mCell = "A1"
mVal = 10
Dim sht As Worksheet
Set sht = wk.Worksheets(mSheet)

With sht
..Range(mCell).Value = mVal
End With
Set sht = Nothing
Set wk = Nothing
End Sub

 

[Tom Ogilvy]

Sub test(mSheet, mVal, mCell)
ActiveWorkbook.Worksheets( _
mSheet).Range(mCell).Value = mVal
End Sub

 

[JLatham]

Gary, just one '.' in
..Range(mCell).Value = mVal
right?

kirkm, this works just as well, although I realize you may have plans for
using this in a more generic form later:
Sub TestIt()
ActiveWorkook.Worksheets('Sheet1').Range("A1") = 10
End Sub

and looking at the long range view, this works for me also
Sub TestSetCellValue()
SetCellValue "Sheet1", "A1",44.92
End Sub
Sub SetCellValue(mSheet as string, mCell as string, mValue as Variant)
'mValue may be text, number or even null as a Variant
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets(mSheet)
With wks
.Range(mCell) = mValue
End With
Set wks = Nothing
End Sub

 

[kirkm]

On Wed, 21 Feb 2007 21:29:06 +1300, kirkm


Thank you very much, one and all.

Sorted and working perfectly.

Cheers - Kirk