Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA Excel loop through selected area

Posted on 2010-01-02
4
Medium Priority
?
511 Views
Last Modified: 2012-05-08
I'm a novice to VBA and am trying to loop through a selected area prompting for a value for each cell.  I'm getting an error.  Attached is the code. Can someone tell me what I'm doing wrong?  
Sub LoopThrough()
Dim c As Range
Dim cellValue As Variant
Dim SelectedCells As Range
Set SelectedCells = Selection
For Each c In Range(SelectedCells)

    cellValue = InputBox("Gimme a value")
    c.Value = cellValue

Next c

End Sub

Open in new window

0
Comment
Question by:BBlu
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
syeager305 earned 120 total points
ID: 26162926
Sub LoopThrough()
Dim c As Range
Dim cellValue As Variant

For Each c In Selection

    cellValue = InputBox("Gimme a value")
    c.Value = cellValue

Next c

End Sub

0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 80 total points
ID: 26162930
Something like the code below will do what you want.

Patrick
Sub LoopThrough()
Dim rng As Range
Dim celle As Range

With Sheets("Sheet1")
    Set rng = Range(.Cells(1, A), .Cells(.rows.count, A).End(xlUp))
End With

For each celle in rng
   celle = InputBox("Gimme a value","Come on I'm waiting") 
Next celle

End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:patrickab
ID: 26162943
Oops that should have been as below...

Patrick
Sub LoopThrough()
Dim rng As Range
Dim celle As Range
 
With Sheets("Sheet1")
'change the next line to suit
    Set rng = Range(.Cells(1, "A"), .Cells(4, "A"))
End With
 
For Each celle In rng
   celle = InputBox("Gimme a value", "Come on I'm waiting")
Next celle
 
End Sub

Open in new window

0
 

Author Closing Comment

by:BBlu
ID: 31672002
syeager305, that was simple and perfect!

patrickab, I can use that more advanced way later.  

Thank you both!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question