Program or quicker way to data entry

peter8848 · Feb 18, 2019

Dear All,

We have data entry from one spreadsheet (A) to another (B) as below:

(A)
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"] [/TD]
[TD="class: xl63, width: 64"]a[/TD]
[TD="class: xl63, width: 64"]b[/TD]
[TD="class: xl63, width: 64"]c[/TD]
[TD="class: xl63, width: 64"]d[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]99[/TD]
[TD="class: xl63, align: right"]99[/TD]
[TD="class: xl63, align: right"]81[/TD]
[TD="class: xl63, align: right"]54[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]67[/TD]
[TD="class: xl63, align: right"]69[/TD]
[TD="class: xl63, align: right"]66[/TD]
[TD="class: xl63, align: right"]94[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]50[/TD]
[TD="class: xl63, align: right"]64[/TD]
[TD="class: xl63, align: right"]91[/TD]
[TD="class: xl63, align: right"]57[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]62[/TD]
[TD="class: xl63, align: right"]59[/TD]
[TD="class: xl63, align: right"]91[/TD]
[TD="class: xl63, align: right"]57[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]90[/TD]
[TD="class: xl63, align: right"]65[/TD]
[TD="class: xl63, align: right"]65[/TD]
[TD="class: xl63, align: right"]92[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]63[/TD]
[TD="class: xl63, align: right"]86[/TD]
[TD="class: xl63, align: right"]66[/TD]
[TD="class: xl63, align: right"]62[/TD]
[/TR]
</tbody>[/TABLE]

(b) Instead of entering the cell as = A1 + B1 + C2 + D5 we need to enter as " = +99 + 99 + 66 + 92,

Is there any quciker way to do it or any possible vba solutions?

Cheers,

Peter

peter8848 · Feb 19, 2019

Hi Akuini,

If possible VBA should enter the data or actual numbers or figures instead of any cell reference.

+ or - dependence on the cell value is positive or negative.

Cheers,

Peter

Akuini · Feb 19, 2019

The cell where you want to enter the data (in workbook B) is it fixed? say it always sheet1 cell A1.
If yes, I think there are 2 options here:

First: in workbook A ,you select some cells (by pressing Enter & click some cells) and the target cell (in workbook B) is fixed, say sheet1 cell A1.

This code will do that:

Code:

Sub a1087966a()

  Workbooks("B").Sheets("Sheet1").Range("A1") = WorksheetFunction.Sum(Selection)
  End Sub

Second, you type some number and the target cell (in workbook B) is also fixed. This will need an Inputbox where you can type some number.

Which one do you choose?

peter8848 · Feb 19, 2019

Hi Akuini,

The data in workbook B it is fixed but instead of a summed final result figure is it possible to keep all the workings too (Each value being added up from the selected range in workbook A)? Like final result is say 200 in workbook B but when you click on workbook B it is like = 100 + 20 +30 +50

Cheers,

Peter

Akuini · Feb 19, 2019

peter8848 said:
Hi Akuini,

The data in workbook B it is fixed but instead of a summed final result figure is it possible to keep all the workings too (Each value being added up from the selected range in workbook A)? Like final result is say 200 in workbook B but when you click on workbook B it is like = 100 + 20 +30 +50

Cheers,

Peter

Ok, try this:
You might want to assign the macro to a keyboard shortcut. So select the cells, hit the shortcut, a message will pop up for confirmation to enter the data.

Code:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1087966a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1087966-program-quicker-way-data-entry.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] r [COLOR=Royalblue]As[/COLOR] Range, tx [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR], msg [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]

tx = [COLOR=brown]"="[/COLOR]
    [COLOR=Royalblue]For[/COLOR] [COLOR=Royalblue]Each[/COLOR] r [COLOR=Royalblue]In[/COLOR] Selection
        tx = tx & [COLOR=brown]"+"[/COLOR] & r
    [COLOR=Royalblue]Next[/COLOR]

tx = Replace(Replace(tx, [COLOR=brown]"+-"[/COLOR], [COLOR=brown]"-"[/COLOR]), [COLOR=brown]"=+"[/COLOR], [COLOR=brown]"="[/COLOR])

    msg = [COLOR=brown]"Do you want to insert this data: "[/COLOR] & vbLf & [COLOR=brown]""""[/COLOR] & tx & [COLOR=brown]""""[/COLOR]
        [COLOR=Royalblue]If[/COLOR] MsgBox(msg, vbOKCancel) = vbCancel [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]

Workbooks([COLOR=brown]"B"[/COLOR]).Sheets([COLOR=brown]"Sheet1"[/COLOR]).Range([COLOR=brown]"A1"[/COLOR]) = tx
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

peter8848 · Jun 5, 2019

Thank you Akuini , you are a Champion!

Akuini · Jun 12, 2019

peter8848 said:
Thank you Akuini , you are a Champion!

You're welcome, glad to help, & thanks for the feedback.

Program or quicker way to data entry

peter8848

Board Regular

peter8848

Board Regular

Excel Facts

Akuini

Well-known Member

peter8848

Board Regular

Akuini

Well-known Member

peter8848

Board Regular

Akuini

Well-known Member

Similar threads

Forum statistics

Share this page

Program or quicker way to data entry

peter8848

Board Regular

peter8848

Board Regular

Excel Facts

Akuini

Well-known Member

peter8848

Board Regular

Akuini

Well-known Member

peter8848

Board Regular

Akuini

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock