visual basic

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi can anyone help,

I want to set up a macro that will give me a list of names on a sheet eg.
Matthew
Mike
David
Etc
But when I ran the macro it remembers the cell I have originally typed into
and I want it to be able run from where i place my mouse eg. It could Start
in cell C1 or D3 etc. Does anyone know the script i would need?
 
It would be helpful to post the macro (or a portion of it if it's long)
in your message. Trying to guess what you recorded is difficult...
 
Here is a sample of the macro


'
ActiveCell.FormulaR1C1 = "Matthew"
Range("R2").Select
ActiveCell.FormulaR1C1 = "Mike"
Range("R3").Select
ActiveCell.FormulaR1C1 = "David"
Range("R4").Select
End Sub
 
One way:

ActiveCell.Value = "Matthew"
ActiveCell.Offset(1, 0).Value = "Mike"
ActiveCell.Offset(2, 0).Value = "David"


another:

ActiveCell.Resize(3, 1).Value = _
Application.Transpose(Array("Matthew", "Mike", "David"))
 
Assuming you're going to be launching this from the starting cell, you can
place all the names in an ARRAY and then loop through the array while
incrementing the row offset value by 1 for each name:


Sub FillIn()
************************************************************
Dim varArray As Variant
Dim varItems As Variant
Dim l As Long

'Place all the names in an Array
varArray = Array("Frank", "Fran", "Issac", "Izzie")
varItems = varArray
'Loop through the array and increment the row
'offset value by 1 after each name insertion
For Each varItems In varArray
ActiveCell.Offset(l).Value = varItems
l = l + 1
Next

End Sub
 
Back
Top