Is this possible in excel sheet

[Ashfaque]

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.
Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

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.....

 

[CJ_London]

perhaps you can use the target.currentregion.columns.count property to determine the number of columns used and then add 1

 

[Ashfaque]

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?
Code Tags Added by UG
Please use Code Tags when posting VBA Code
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

'========

'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
'=======

 

[CJ_London]

I was thinking something more like

Cells(1, Range("A1").CurrentRegion.Columns.Count + 1) = Range("a1")

 

[Ashfaque]

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

 

[CJ_London]

I don't think I quite understand what you are trying to achieve - suggest provide some before and after screenshots to illustrate your requirement

 

[Ashfaque]

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..

 

[CJ_London]

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

 

[Ashfaque]

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

 

[CJ_London]

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.

 

[Ashfaque]

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....

 

[CJ_London]

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

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

 

[Gasman]

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?

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]

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

 

[CJ_London]

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")

 

[Ashfaque]

Thanks CJ again,

Yes it is working up to second next cell only. But I need to expand it further...means i after B5 it should copy to B6, B7 and so on... may a loop is required....

 

[Ashfaque]

CJ,

I changed a bit to your given line as follows and it worked.

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

But I dont know how long it will work as I need it to stop somewhere....means after 30 or 35 cell it should stop.

 

[CJ_London]

you haven't said what you want to do when you reach this number so use an if statement - if rows.count>35 then do nothing.

 

[Ashfaque]

Yes, you are correct.

I want to stop it once the number of cell reaches to 20. Because 20 is my max number I will use. Thereafter some other calculations shall be carried out.

 

[CJ_London]

not sure if you are asking a question - I've given you guidance - just change the 35 to 20 and whatever code after that to do your calculation.

I'm happy to point you in the right direction, not do your job for you