PDA

View Full Version : Vba to insert text in range of cells



4hoteliers
02-08-2016, 10:30 AM
I need to insert the below text in a range of 5 cells in a workbook that contains 6 sheets

Cell X2 : FIT
Cell X3 : WEB TO
Cell X4 : TO
Cell X5 : TA
Cell X6 : IDS
Cell X7 : OWN

Thanks a lot for the help

rstuck
02-08-2016, 10:35 AM
The best way I found to see how the VBA might look is to record a macro and manually do it once. Then you can modify the new macro.
Now where is this information stored? is this going to be inserted in every sheet? or just one sheet?

4hoteliers
02-08-2016, 10:43 AM
the text is not stored anywhere...i need this text to be inserted in the range X2:X7 in each sheet

rstuck
02-08-2016, 11:05 AM
here is a dirty sample. you could make this better if you made the words into constant variables and then looped through the spreadsheets.


Range("W2").Select
ActiveCell.FormulaR1C1 = "Fit"
Range("W3").Select
ActiveCell.FormulaR1C1 = "Web To"
Range("W4").Select
ActiveCell.FormulaR1C1 = "To"
Range("W5").Select
ActiveCell.FormulaR1C1 = "TA"
Range("W6").Select
ActiveCell.FormulaR1C1 = "IDS"
Range("W7").Select
ActiveCell.FormulaR1C1 = "OWN"
Range("W2:W7").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveWindow.SmallScroll ToRight:=12
Range("X2").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveWindow.SmallScroll ToRight:=11
Range("X2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
ActiveWindow.SmallScroll ToRight:=12
Range("X2").Select
ActiveSheet.Paste
Sheets("Sheet4").Select
ActiveWindow.SmallScroll ToRight:=10
Range("W2").Select
ActiveSheet.Paste
Sheets("Sheet5").Select
ActiveWindow.SmallScroll ToRight:=11
Range("X2").Select
ActiveSheet.Paste

4hoteliers
02-08-2016, 11:26 AM
thank you, will try and let you know

SamT
02-08-2016, 07:18 PM
Sub SamT()
Dim Sht As Worksheet
For each Sht in Worksheets
Sht.Range.("X2") = Array("FIT", "WEB TO", "TO", "TA", "IDS", "OWN")
Next'
End Sub

4hoteliers
02-08-2016, 09:17 PM
SamT hello,
thank you for the code.
when i run it comes out the error:

Compile error:
Expected: identifier or bracketed expression

SamT
02-09-2016, 12:05 AM
Sub SamT()
Dim Temp
Dim Sht As Worksheet
For Each Sht In Worksheets
Temp = Array("FIT", "WEB TO", "TO", "TA", "IDS", "OWN")

Sht.Range("X2").Resize(6) = Application.Transpose(Temp)
Next '
End Sub

4hoteliers
02-09-2016, 12:25 AM
Thank you SamT, works fine now

snb
02-09-2016, 01:02 AM
Why don't you stick to ?

http://www.mrexcel.com/forum/search.php?searchid=4124480

4hoteliers
02-09-2016, 02:08 AM
hello snb, what do you mean???