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

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.