PDA

View Full Version : [SOLVED] Using InputBox to select Range



DanOfEarth
06-27-2010, 07:42 AM
I definitely knew this was wrong before I wrote it, but I can't seem to grasp what the syntax should be.

I need to use the InputBox value for the Row#....


Sub ImportData()
Dim FileName As String
Dim filepath As String
Dim MyRow As Integer
MyRow = InputBox("What Row Number?")
Application.ScreenUpdating = False
Workbooks.Open ("C:\Documents and Settings\Dan\Desktop\SSInfo.xls")
Worksheets("Sheet1").Range("B2:AA2").Copy _
ThisWorkbook.Activate
ActiveWorkbook.Worksheets("Import").Range("B"&"MyRow":"AA"&"MyRow").Paste
Application.ScreenUpdating = True
End Sub

I've done this many times before....or maybe I should use the "cell" methods....

DanOfEarth
06-27-2010, 08:37 AM
Got it!!

I think I got a brain virus. I can't figure out something, regardless of web-search, unless I post it on this forum.

Then lightning strikes.

Fixed using:



.Range("C3").Formula = wb.Worksheets("Sheet1").Cells(MyRow, 2).Formula

mdmackillop
06-27-2010, 11:38 AM
Set variables for workbooks when working between them. It helps avoid confusion. Also, paste to a single cell, rather than a range.


Sub ImportData()
Dim MyRow As Long
Dim wbSource As Workbook
Dim tgt As Range
MyRow = InputBox("What Row Number?")
Set tgt = Sheets("Import").Range("B" & "MyRow")
Set wbSource = Workbooks.Open("C:\Documents and Settings\Dan\Desktop\SSInfo.xls")
wbSource.Sheets("Sheet1").Range("B2:AA2").Copy tgt
End Sub