Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-30-2019, 12:40 PM   #1
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Is this possible in excel sheet

Hi,

As per belwo code, I am typing something in cell A4 of excel sheet and B4 is filling with current date with today's date. As long as you go down inuting values in A column cells, B column cells will be filled with date automatcally.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Target.Row < 3 Then Exit Sub

If Not Intersect(Target, Columns("A:A")) Is Nothing Then

Application.EnableEvents = False

If Len(Trim(Target.Row)) > 0 Then

'========

'If (Target.Offset(0, 1).Value) > 0 Then
Target.Offset(0, 1).Value = Date
'Else
'Target.Offset(1, 1).Value = Date
'End If

Else
Target.Offset(0, 1).Value = vbNullString
'=======
End If

Application.EnableEvents = True

End If

End Sub

What I need now is I want to change the value in A4 whenever required and the value of A4 need to copied to cell B4.
When next time I change the value of same A4 cell, I want to check if B4 has any value then it should take next cell in same column and fill the value and go on...
I am stucked at the remark code lines. Can some one see please.

The code is working fine with dates in next below cell but we need to input value in A column parallal

Is there anyone can extend help please...

Or after changing value in A4 a button can be set somewhere and after pressing this btn, the value of A4 shall be copied to B4 and if B4 is already contain value then B5.....B6......and so on.

Can someone help me out please.....
Attached Files
File Type: xls DATE ENTERING SHEET.xls (27.0 KB, 15 views)

Ashfaque is offline   Reply With Quote
Old 01-30-2019, 03:16 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,491 Times in 3,378 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Is this possible in excel sheet

perhaps you can use the target.currentregion.columns.count property to determine the number of columns used and then add 1
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-30-2019, 09:32 PM   #3
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Is this possible in excel sheet

Hi,

Thanks CJ

I tried the way you asked for and modified the code a bit. It is working but only up to one next cell. Mean 2 cell it is filling up with the value. But if both cells are with date in same column, it should take 3rd cell. Possibly it need For Next loop. But how?

'========

'If (Target.Offset(0, 1).Value) > 0 Then

If Target.CurrentRegion.Columns.Count > 0 Then

If Target.Offset(0, 1).Value > 0 Then
Target.Offset(1, 1).Value = Target
Else
Target.Offset(0, 1).Value = Target
End If

End If

Else
'Target.Offset(0, 1).Value = vbNullString

Target.CurrentRegion(1, 1).Value = vbNullString
'=======

Ashfaque is offline   Reply With Quote
Old 01-31-2019, 02:33 PM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,491 Times in 3,378 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Is this possible in excel sheet

I was thinking something more like

Cells(1, Range("A1").CurrentRegion.Columns.Count + 1) = Range("a1")
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 01-31-2019, 03:32 PM   #5
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Is this possible in excel sheet

Thanks again CJ

With your code line it is representing result but in cells from left to right at the first line...... I think there should be use of Do While T or something like.....

We need result to be in one column one below one cells
Ashfaque is offline   Reply With Quote
Old 01-31-2019, 04:27 PM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,491 Times in 3,378 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Is this possible in excel sheet

I don't think I quite understand what you are trying to achieve - suggest provide some before and after screenshots to illustrate your requirement
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-01-2019, 06:27 AM   #7
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Is this possible in excel sheet

I have attached sheet to review.

I need Yellow A4 cell value to appear in Blue cell (B4) one below one when ever changed. But should check if B4 has value then copy the value to B5, B6 and so on. This is intial requirement. Later on we will place a btn on sheet and place the same code behind to take effect rather than taking effect after change in cell

In other words, the value in A4 might not correct so we need to think and may be it need change. So till we click the btn value should not transfer to B4, B5 etc..
Attached Files
File Type: xls DATE ENTERING SHEET.xls (33.0 KB, 11 views)

Ashfaque is offline   Reply With Quote
Old 02-01-2019, 07:11 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,491 Times in 3,378 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Is this possible in excel sheet

you need to provide a clearer explanation, I'm no clearer than I was from your first explanation.

My understanding is:
1. Starting with an empty spreadsheet, you enter a value in A4. todays date appears in B4.
2. tomorrow you enter another value in A4. or the same value again? You want to move the date in B4 to B5 and repopulate B4 with the current (i.e. tomorrows) date.
3. the day after you enter a value in A4, or the same value again? the dates in B4 and B5 are moved down a row and B4 is populated with the current date

and so on - is that what you require?

perhaps explain what the purpose of this exercise is so I can put it in context
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-01-2019, 07:59 AM   #9
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Is this possible in excel sheet

Sir,
Yes correct. I need new values in B5, B6, and so on. Date is just an example. But the value will be in numbers.

Basically it is part of estimate calculation of construction cost. So the A4 cell will have different values.
Whatever value i feed in A4 it should store in B4. Let it be date, text or number
Mostly it will be calculated number.

If i change or replace the value of A4, the new value should be copied in B4. But if the B4 is already contains previous value then it should copy the value in next cell which is B5, and so on...

I hope it ia clear now

Last edited by Ashfaque; 02-01-2019 at 08:05 AM.
Ashfaque is offline   Reply With Quote
Old 02-01-2019, 10:11 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,491 Times in 3,378 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Is this possible in excel sheet

Quote:
If i change or replace the value of A4, the new value should be copied in B4. But if the B4 is already contains previous value then it should copy the value in next cell which is B5, and so on...

I hope it ia clear now
regret not. I do not understand your written requirement. If you are unable to demonstrate your requirement I cannot help.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-01-2019, 10:30 AM   #11
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Is this possible in excel sheet

Value coping steps after A4 changes its value.

A4 --> B4 ....Value of A4 will be copied to B4
A4 --> B5 .... Value of A4 will ONLY be copied to next cell B5 if previous cell B4 has value in it. Otherwize it will copy B4 value
A4 --> B6 .... Value of A4 will ONLY be copied to next cell B6 if prevous cell B5 has value in it. Otherwize it will copy B5
A4 --> B7

And so on....
Ashfaque is offline   Reply With Quote
Old 02-01-2019, 10:41 AM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,491 Times in 3,378 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Is this possible in excel sheet

Final effort - provide an example, not a written description. You are using excel, mock it up. I'm sure you understand what is required, but your description does not make sense


Quote:
A4 --> B5 .... Value of A4 will ONLY be copied to next cell B5 if previous cell B4 has value in it. Otherwize it will copy B4 value
previously you were saying copy B4 to B5 then A4 to B4
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 02-01-2019, 10:41 AM   #13
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,596
Thanks: 387
Thanked 620 Times in 601 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Is this possible in excel sheet

If I read that correctly, if the cell in column B is empty it is copied to the next row? So the same value is copied just either via A4 or via B4?

Quote:
Originally Posted by Ashfaque View Post
Value coping steps after A4 changes its value.

A4 --> B4 ....Value of A4 will be copied to B4
A4 --> B5 .... Value of A4 will ONLY be copied to next cell B5 if previous cell B4 has value in it. Otherwize it will copy B4 value
A4 --> B6 .... Value of A4 will ONLY be copied to next cell B6 if prevous cell B5 has value in it. Otherwize it will copy B5
A4 --> B7

And so on....
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-01-2019, 04:34 PM   #14
Ashfaque
Student
 
Ashfaque's Avatar
 
Join Date: Sep 2004
Location: Nanded, India
Posts: 498
Thanks: 5
Thanked 14 Times in 14 Posts
Ashfaque is an unknown quantity at this point
Send a message via Yahoo to Ashfaque
Re: Is this possible in excel sheet

Gentlmen,

Please forget all I wrote previously...may be I did not explain properly.

I will give a practile example now.

Let us assume I have opened one new excel sheet. No data in no cell initially there. OK

Now I put the some value in A4 cell....let us say 150. After entering this value in A4 cell, this 150 should copy automatically to Cell B4

150 (A4) = 150 (B4)

But if I changes value in A4 (same cell where I wrote 150) and overwrite as 190 then this changed value which is 190 should copy in B5 cell.

Now why it should copy to B5 the next cell because B4 cell already have previous stored value which is 150

Similarly I again changes value of A4 cell as 2002 then this time it should copy this 2002 to B6 cell because previous B4 & B5 are already have stored values

This is the best explanation I can provide.... please see the attached file
Attached Files
File Type: xls MY SHEET.xls (34.0 KB, 11 views)
Ashfaque is offline   Reply With Quote
Old 02-01-2019, 06:11 PM   #15
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,766
Thanks: 40
Thanked 3,491 Times in 3,378 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Is this possible in excel sheet

from your description it sounds like my suggestion in post #2 and expanded on in post #4 should do the job, but use rows.count rather that columns.count and adjust other factors because it is a column you are populating not a row.


Cells(Range("A4").CurrentRegion.Rows.Count+3,2) = Range("a4")

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
add row to excel sheet Cowboy_BeBa Modules & VBA 3 08-16-2015 11:59 AM
Copy 2 rows from various excel sheet and paste it onto one sheet irfanparbatani Excel 1 11-29-2011 03:53 PM
Excel automation for detecting new sheet and extracting data to another sheet germaine Excel 5 05-10-2010 10:41 AM
Excel sheet invisible fredalina Modules & VBA 1 12-18-2008 12:10 PM
formatting excel sheet dinger Modules & VBA 1 10-14-2005 01:38 AM




All times are GMT -8. The time now is 02:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World