By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,630 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,630 IT Pros & Developers. It's quick & easy.

Edexcel - Miscrosoft Visual Basic help

P: 118
In Excel I have a "Home" page with cells in which you insert:
Customer Name
Customer Type (drop down type for 'Standard', 'Business man' and 'Tour Group'.)
Nights Staying
Room Number (Drop Down for room numbers; from Page "Customers" in which the numbers are in a named range)
and Date Of Arrival

I have a basic VB code to copy the details from the Home page to the Customers page. However, at the moment it only copies them to one place in the Customers page.

How would I link them to the cells next to the named range (Customers page) where the number is the same as in the drop down 'Room Number'.

So for example someone selects room '7' in the drop down list, then writes their details. How would I get it so, when they press add (run the Macro) the details are pasted (or moved to) in, say, cell C13 which is next to number 7 from the range.. and therefore the same for every/any number that is in the drop-down, goes to the same number in the Customers page.

I hope that makes sense.

Mar 26 '07 #1
Share this Question
Share on Google+
2 Replies

P: 118
So far I have written:

Expand|Select|Wrap|Line Numbers
  1.  If Range("G28").Value = "1" Then
  2.     Sheets("Home").Select
  3.     Range("G19:I19").Select
  4.     Selection.Copy
  5.     Sheets("Customers").Select
  6.     Range("C7").Select
  7.     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  8.     xlNone, SkipBlanks:=False, Transpose:=False
  9.     Sheets("Home").Select
  10.     Range("G22").Select
  11.     Selection.Copy
  12.     Sheets("Customers").Select
  13.     Range("F7").Select
  14.     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  15.     xlNone, SkipBlanks:=False, Transpose:=False
  16.     Sheets("Home").Select
  17.     Range("G25").Select
  18.     Selection.Copy
  19.     Sheets("Customers").Select
  20.     Range("J7").Select
  21.     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  22.     xlNone, SkipBlanks:=False, Transpose:=False
  23.     Sheets("Home").Select
  24.     Range("G31").Select
  25.     Selection.Copy
  26.     Sheets("Customers").Select
  27.     Range("H7").Select
  28.     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  29.     xlNone, SkipBlanks:=False, Transpose:=False
  30.         Exit Sub
  31.     End If
So far this works but since there are 50 'rooms' + 7 other rooms in the drop down.. I will have to do this code 57 times over. Is there an easier way to get the number to correspond with the same number in the next page (details in first post)?


Mar 26 '07 #2

Expert 5K+
P: 8,434
While there are probably more elegant ways to achieve this, I'm not all that familiar with using Excel features from VBA. A simple brute-force method would be to use a FOR loop to run through the range of cells, and handle each one in turn.

The section of code which would have been repeated 57 times is then required only once, either within the loop of in a separate Sub or Function which you call from inside the loop.
Mar 27 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.