Computing.Net > Forums > Programming > Excel Vba Code

Excel Vba Code

Reply to Message Icon

Original Message
Name: welshboo
Date: November 16, 2006 at 04:10:04 Pacific
Subject: Excel Vba Code
OS: win xp
CPU/Ram: ??
Model/Manufacturer: e machines
Comment:

i have been trying for days to create a multiplication table in Excel using Visual basic it is driving me mad!! i has to be a 12x12 grid with each row the correct multiplication etc i have only just started using this and woud really appreciate some help please, this is my attempt but it doesnt work or print any numbers or anything can someone show me what the correct code is ????

'Input1 is referred to as [B1]
'Input2 is referred to as [B2]
'Input3 is referred to as [B3]
'Input4 is referred to as [B4]
'Input5 is referred to as [B5]
'Input6 is referred to as [B6]
'Input7 is referred to as [B7]
'Input8 is referred to as [B8]
'Input9 is referred to as [B9]
'Input10 is referred to as [B10]
'Input11 is referred to as [B11]
'Input12 is referred to as [B12]


Option Explicit

'clears the Output
Sub ClearOutput()
[B5] = Chr(0)
End Sub

'reads name in Input1 and displays greeting in Output
Sub hello()
Dim strName As String 'name read in
Dim strGreeting As String ' greeting displayed

strName = [B1]
strGreeting = "hello " & strName

[B5] = strGreeting
End Sub

'adds the numbers in Input1 and Input2 and displays sum in Output
Sub AddNumbers()
Dim intFirst As Integer, intsecond As Integer 'numbers read in
Dim intSum As Integer 'sum displayed

intFirst = [B1]
intsecond = [B2]
intSum = intFirst + intsecond

[B5] = intSum
End Sub
'creates a multiplication table
Sub TimesTable()

Dim row As Integer
Dim collumn As Integer
Dim intAnswer As Integer
Dim strAnswer As String
Dim intFirst As Integer
Dim intsecond As Integer
Dim FormatintAnswer As FormatCondition
Dim strOut As String


intFirst = [B1]
intsecond = [B2]

intFirst = 1
Do
intsecond = 1
Do
intAnswer = intFirst * intsecond

strOut = strOut & strAnswer
intsecond = intsecond + 1

Loop Until intsecond = 13
strOut = strOut & Chr(10)

intFirst = intFirst + 1

Loop Until intFirst = 13


End Sub


Report Offensive Message For Removal


Response Number 1
Name: welshboo
Date: November 16, 2006 at 04:15:21 Pacific
Reply: (edit)

creates a multiplication table
Sub TimesTable()

Dim row As Integer
Dim collumn As Integer
Dim intAnswer As Integer
Dim strAnswer As String
Dim intFirst As Integer
Dim intsecond As Integer
Dim FormatintAnswer As FormatCondition
Dim strOut As String


intFirst = [B1]
intsecond = [B2]

intFirst = 1
Do
intsecond = 1
Do
intAnswer = intFirst * intsecond

strOut = strOut & strAnswer
intsecond = intsecond + 1

Loop Until intsecond = 13
strOut = strOut & Chr(10)

intFirst = intFirst + 1

Loop Until intFirst = 13


End Sub


Report Offensive Follow Up For Removal

Response Number 2
Name: jon_k
Date: February 20, 2007 at 05:02:12 Pacific
Reply: (edit)

sub mult()

For icol=1 to 12
for irow=1 to 12
varval = icol * irow
sheet1.cells(irow+1,icol+1).formula = varval
next irow
next icol

end sub

Not terribly efficient, and I've left a space for column and row headers.

You could equally use on the long line:

sheet1.cells(selection.row+irow-1,selection.column+icol-1).formula = varval

This will make the 12*12 grid appear wherever your cursor is. Don't do it on the last 11 columns or rows of the sheet, though. It will fall over.


Report Offensive Follow Up For Removal







Use following form to reply to current message:

   Name: From My Computing.Net Settings
 E-Mail: From My Computing.Net Settings

Subject: Excel Vba Code 

Comments:

 


  Homepage URL (*): 
Homepage Title (*): 
         Image URL: 
 
Data Recovery Software




Have you ever used OpenOffice?

Yes, as my main suite.
Yes, occationally.
Yes, but only once.
No, never.


View Results

Poll Finishes In 5 Days.
Discuss in The Lounge