Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › New to Visual Basic...need some help
New Posts  All Forums:Forum Nav:

New to Visual Basic...need some help

post #1 of 9
Thread Starter 
I've been sitting here for 45 minutes trying to figure out what this piece of code does.
Code:
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = InputBox("What is your surname?")

I mainly want to know what the following does: (Rows.Count).End(xlUp).Offset(1).Select
I have a fair understanding of the second line.

Any insight would be greatly appreciated.
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
post #2 of 9
I am assuming (because it's not particularly clear) that this relies upon Excel interop?

In which case it likely selects several cells on the spreadsheet (possibly just one) and then adds an input box to the cell(s).

It looks like it would be the last row of the spreadsheet (in column A) to whatever x1Up is, probably a variable. I don't know what offset does in this context, might be helpful to look up the Excel interop docs.
Micro Dance
(8 items)
 
  
CPUMotherboardGraphicsRAM
i7-4790k z97i-plus EVGA GTX980 SC 16GB Team Xtreme 2400 
Hard DriveCoolingOSCase
512GB Samsung SSD 830 NH-L12 Windows 10 EVGA Hadron Air 
  hide details  
Reply
Micro Dance
(8 items)
 
  
CPUMotherboardGraphicsRAM
i7-4790k z97i-plus EVGA GTX980 SC 16GB Team Xtreme 2400 
Hard DriveCoolingOSCase
512GB Samsung SSD 830 NH-L12 Windows 10 EVGA Hadron Air 
  hide details  
Reply
post #3 of 9
Thread Starter 
Quote:
Originally Posted by Xeio View Post

I am assuming (because it's not particularly clear) that this relies upon Excel interop?

In which case it likely selects several cells on the spreadsheet (possibly just one) and then adds an input box to the cell(s).

It looks like it would be the last row of the spreadsheet (in column A) to whatever x1Up is, probably a variable. I don't know what offset does in this context, might be helpful to look up the Excel interop docs.

Okay, I don't understand. Sorry for being unclear. I'm new to this. :\

Below is the entire code if it clears things up a bit. The purpose of this code is to prompt patients of a pretend dental office on the treatment they have received, and store the info in an Excel spreadsheet.
Quote:
The form needs to prompt patients to enter the following information: their surname, their dentist’s surname, the treatment they have received, the date they received it and a Yes or No response to the following question: Are you happy with our service?
Warning: Spoiler! (Click to show)
Code:
Sub Customer_Service()
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = InputBox("What is your surname?")
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = InputBox("What is the name of your dentist?")
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = InputBox("What treatment did you receive?")
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = InputBox("What was the date of your treatment? (DD/MM/YY)")

x = MsgBox("Were you happy with our service?", 4)
If x = 7 Then
Range("E" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "No"
Else
Range("E" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Yes"
End If
End Sub

I really want to get the hang of VB, but I think I'm off to a slow start.
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
post #4 of 9
Thread Starter 
Okay, first things first. What does Range("A" & Rows.Count). do?

Secondly, what purpose does the "End" serve in that line?

Thirdly, I know what xlUp means, it is short of Excel Up. It is a directional constraint, but what purpose does it serve here?

Fourth, I've found answers for this, but it wasn't in English. What does FormulaR1C1 do?

If I understand correctly, Offset(1) marks the row next to the currently active row as active?
What does Offset(1) do?

Figured it out...with the 1 in bracket, it would select 1 cell under a used cell. Thanks to Range("A"...( still don't know what Rows.Count do) it will be in column A. Am I right?
Edited by vinton13 - 6/24/13 at 8:39pm
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
post #5 of 9
Rows is mostly likely some sort of object that represents every row in the spreadsheet (these don't actually go to infinity, this would probably only count rows that have data or have been modified). So Rows.Count is the number of rows on the current spreadsheet.

So if there are 3 rows in the spreadsheet, it would call Range("A3"), which should select the A3 cell. It appears .End(xlUp) just then selects the first row again... And then Offset(1) appears to just move the selection down by one row...

You'd have to run it to see if that's what it's actually selecting, but it looks like it's just going to select the first or second cell in the A column and add an input field. I've only very limitedly used excel interop. You might want to look at this.
Micro Dance
(8 items)
 
  
CPUMotherboardGraphicsRAM
i7-4790k z97i-plus EVGA GTX980 SC 16GB Team Xtreme 2400 
Hard DriveCoolingOSCase
512GB Samsung SSD 830 NH-L12 Windows 10 EVGA Hadron Air 
  hide details  
Reply
Micro Dance
(8 items)
 
  
CPUMotherboardGraphicsRAM
i7-4790k z97i-plus EVGA GTX980 SC 16GB Team Xtreme 2400 
Hard DriveCoolingOSCase
512GB Samsung SSD 830 NH-L12 Windows 10 EVGA Hadron Air 
  hide details  
Reply
post #6 of 9
Thread Starter 
Okay, thank you. I will work with this for now.
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
post #7 of 9
Quote:
Originally Posted by vinton13 View Post

Okay, first things first. What does Range("A" & Rows.Count). do?

Secondly, what purpose does the "End" serve in that line?

Thirdly, I know what xlUp means, it is short of Excel Up. It is a directional constraint, but what purpose does it serve here?

Fourth, I've found answers for this, but it wasn't in English. What does FormulaR1C1 do?

If I understand correctly, Offset(1) marks the row next to the currently active row as active?
What does Offset(1) do?

Figured it out...with the 1 in bracket, it would select 1 cell under a used cell. Thanks to Range("A"...( still don't know what Rows.Count do) it will be in column A. Am I right?

VBA's docs are actually pretty good. So just select the term you want to understand, then press F1 and you'll be presented with a description of what it does and code examples for how it's used.

But to answer your questions:
  1. Rows is an object for a range of rows (sounds like I'm just rewording it's name, but that's literally all it is). Saying Rows.Count is saying "count the number of rows selected"
  2. The End (combined with xlUp) is basically saying go to the last row/column with data in it (or it might be last row/column before an empty row/column - it's been a while since I've done any VBA)
  3. The xlUp i think is just simulating keyboard control. But either way, I've answered question 3 in answer 2
  4. FormulaR1C1 basically just means that the value your assigning is an Excel formula. The R1C1 defines the type of formula it is (Excel supports two types of vectors: 1) where you define cells by rows and column addresses (eg A1) 2) where you define cells in relation to each other (R1C1). This method means 1 row down, 1 column along. So to move multiple cells you'd have R3C2 or R-3C-2 to move multiple cells along in the opposite direction (remember, the R1C1 method is relational - where as the A1 method is absolute). The code in your example is technically wrong as it's assigning a surname as a formula. Technically the code should be ActiveCell.Value = InputBox("What is your surname?") (same for all the other times the developer has used the FormulaR1C1 function - they're all used incorrectly in that code and should be changed to .Value =


But in all honesty, the help function in Excel's VBA IDE is excellent. You'll find that much more use than asking on here smile.gif
post #8 of 9
Thread Starter 
I did some digging around, and yes, the Excel's help function is pretty good!
IMO VB is a bit harder than previous [web] languages I've worked with. But I always find these things fun.

I've figured out xlup, xldown, xltoleft and xltoright and FormulaR1C1 through playing around with the code, but the explanation about it being paired with End is pretty helpful, and Rows.Count too. Thank you! smile.gif
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
Apollo
(16 items)
 
Project Greyscale
(24 items)
 
Juan
(16 items)
 
CPUMotherboardGraphicsRAM
Intel Core i7-5930K Asus Sabertooth X99 ASUS GeForce GTX 1080 8GB ROG STRIX  Corsair Vengeance 32GB (4x8GB) DDR4 3000 
Hard DriveHard DriveOptical DriveCooling
Samsung 850 EVO 500GB Samsung 850 EVO 1TB Samsung SH-224BB CM Hyper 212 EVO 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium (Cherry Red) Corsair AX850 
CaseMouseMouse PadAudio
Cooler Master HAF X Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC1150 8-Channel + Logitech z906 + Sam... 
CPUMotherboardGraphicsGraphics
i7-4790K EVGA Z97 Classified EVGA GTX 780Ti EVGA GTX 780Ti 
RAMHard DriveHard DriveHard Drive
Dominator Platinum Spinpoint F3 Spinpoint F3 510 Series 
Hard DriveHard DriveCoolingCooling
510 Series 320 Series XSPC AX120 XSPC AX360 
CoolingCoolingCoolingOS
XSPC AX240 EK Supremacy EK FC780 GTX Ti WIndows 8.1 
OSMonitorPowerCase
OS X 10.9.3 Yamaski Catleap 120Hz HCP-1200 Air 540 
MouseMouse PadAudioAudio
Razer Deathadder Table Avid Fast Track Duo HiFIMan HE-400 
CPUMotherboardGraphicsRAM
AMD FX-8120 @4.5GHz GIGABYTE GA-990FXA-UD7 GIGABYTE Radeon HD 7950 CrossFired G.SKILL Sniper 1600MHz 2x4GB 
Hard DriveHard DriveOptical DriveCooling
Crucial 64GB m4 1TB WD Black Samsung SH-224BB CM Hyper 212+ 
OSMonitorKeyboardPower
Windows 8.1 x64 Pro Asus PB278Q (1440p) Gigabyte Aivia Osmium Corsair AX850 
CaseMouseMouse PadAudio
CM 690II Gigabyte Aivia Krypton Razer Goliathus Extended Speed Edition Realtek ALC889 + Logitech z906 + Samson SR850 
  hide details  
Reply
post #9 of 9
Quote:
Originally Posted by vinton13 View Post

I did some digging around, and yes, the Excel's help function is pretty good!
IMO VB is a bit harder than previous [web] languages I've worked with. But I always find these things fun.

I've figured out xlup, xldown, xltoleft and xltoright and FormulaR1C1 through playing around with the code, but the explanation about it being paired with End is pretty helpful, and Rows.Count too. Thank you! smile.gif

Not a problem. And playing around with the code to see what happens is definitely the best way to learn thumb.gif (or at least the most fun laugher.gif)
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › New to Visual Basic...need some help