PDA

View Full Version : [SOLVED] VBA exel programming: how to make a automatically filled up by filling up other cell



baralus
02-17-2018, 12:36 PM
So I'm very new in using VBA in excel, so I post this question to ask how to program the worksheet so that if I type "1 or any number" for example on cell "A1". Cell "B1 to K1" will automatically produce answers from multiplying the number on "A1" from "1 to 10" starting from "B1 to K1".

21638

So its like a making a multriplication table but i can change it by simple changing the numbers in column A. Im sorry if I cant post any sample code that im writing, it's just that im very new and i really dont know if what im coding is going to get me their or even close. Im trying to learn by imagining my own scenarios, hope you can give me some sample codes or ideas. Thanks

mana
02-18-2018, 01:13 AM
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rr As Range
Dim r As Range

Set rr = Intersect(Columns(1), Target)
If rr Is Nothing Then Exit Sub

Application.EnableEvents = False

For Each r In rr
With r.Offset(, 1).Resize(, 10)
.FormulaR1C1 = "=iferror(if(rc1="""","""",rc1*column(rc[-1])),"""")"
.Value = .Value
End With
Next

Application.EnableEvents = True

End Sub



マナ

baralus
02-18-2018, 07:32 AM
thank you very much sir, i will study this code and try to apply it in other ways..

baralus
03-03-2018, 11:17 AM
Sir may i ask what is the used of "iferror" function in the formular1c1 and how it multiplies the value you specify from 1 to 10. Because everything i research about "iferror" function is that it handles error by returning to the value you specify

baralus
03-06-2018, 05:32 AM
Sir may i ask what is the used of "iferror" function in the formular1c1 and how it multiplies the value you specify from 1 to 10. Because everything i research about "iferror" function is that it handles error by returning to the value you specify

SamT
03-06-2018, 08:48 AM
Cell "B1 to K1" will automatically produce answers from multiplying the number on "A1" from "1 to 10" starting from "B1 to K1".
Formula in B1 = "=A1"
Formula in C1 = "=$A1+B1"
Copy/Fill C1 across to K1

Copy/fill B1 to K1 down as needed


Using BA only: In Worksheet Code Module...

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 1 Then Exit Sub
If not IsNumeric(Target) then exit Sub
If Target = 0 Then Exit Sub

Dim i As Long
Dim Answers As Variant
ReDim Answers(1 To 10)

For i = 1 To 10
Answers(i) = Target * i
Next

Target.Offset(0, 1).Resize(1, 10) = Answers
End Sub

DoubleClick cell in A to run

baralus
03-09-2018, 09:54 PM
Thank you sir, really good help