Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-18-2016, 09:25 AM
theexpat theexpat is offline Excel cells changing values Windows 7 32bit Excel cells changing values Office 2007
Novice
Excel cells changing values
 
Join Date: Feb 2016
Posts: 6
theexpat is on a distinguished road
Smile Excel cells changing values

please help me on this

Firstly , Cell B1 will take the value from A1
then if A1 value changed ,the new value will be in Cell B2.(B1 will not change)


if A1 changed again ,it's value will be in Cell B3.(B1,B2 will not change)
if A1 changed Again ,it's value will be in Cell B4.(B1,B2,B3 will not change)
and so on

please can you do it by formula, if not by VB macro.


thank you in advance
Reda.
Reply With Quote
  #2  
Old 02-24-2016, 05:08 AM
Philb1 Philb1 is offline Excel cells changing values Windows 10 Excel cells changing values Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

Hello theexpat

I'm one of those as well

Put this code in the sheet module by right clicking the sheet tab, click view code & paste it in the module.
Enter whatever in A1 press enter & do it again etc

Cheers

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Target2 As Range
Dim Lrow As Long
Set Target2 = Me.Cells(1, 1) ' Cell A1

On Error GoTo ExitOut

If Not Intersect(Target, Target2) Is Nothing Then

    Application.EnableEvents = False
    
    If IsEmpty(Target2.Offset(, 1)) Then
        Target2.Offset(, 1) = Target
        GoTo ExitOut
        
    Else
        Lrow = Me.Columns(2).Find(What:="*", LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlPrevious, _
                MatchCase:=False, SearchFormat:=False).Row
        
        Target.Offset(Lrow, 1) = Target2
        GoTo ExitOut
    End If
    
    Else
    GoTo ExitOut
End If

ExitOut:
Application.EnableEvents = True
On Error GoTo 0
Exit Sub

End Sub
Attached Files
File Type: xlsm A1ChangeExample.xlsm (15.7 KB, 12 views)
Reply With Quote
  #3  
Old 02-26-2016, 07:53 AM
theexpat theexpat is offline Excel cells changing values Windows 7 32bit Excel cells changing values Office 2007
Novice
Excel cells changing values
 
Join Date: Feb 2016
Posts: 6
theexpat is on a distinguished road
Default

Hello Philb1

highly appreciated - thank you.
Reply With Quote
  #4  
Old 02-26-2016, 12:58 PM
Philb1 Philb1 is offline Excel cells changing values Windows 10 Excel cells changing values Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

No Probs
Reply With Quote
  #5  
Old 02-27-2016, 10:28 PM
theexpat theexpat is offline Excel cells changing values Windows 7 32bit Excel cells changing values Office 2007
Novice
Excel cells changing values
 
Join Date: Feb 2016
Posts: 6
theexpat is on a distinguished road
Default

One more help please
I want to make it for any cell.
Reply With Quote
  #6  
Old 02-27-2016, 10:36 PM
Philb1 Philb1 is offline Excel cells changing values Windows 10 Excel cells changing values Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

What do you mean? Explain please
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel cells changing values how to unhide cells with "0" values in excel 2013 msnarayanan Excel 4 07-29-2015 06:16 AM
Excel cells changing values Merging cells and keeping all the data/values Holgate1967 Excel 1 11-29-2013 09:37 AM
Changing resources against tasks with actual values joday48 Project 3 06-28-2012 05:13 AM
Change values in cells based on criteria SaneMan Excel Programming 2 02-02-2012 07:58 AM
HELP! Powerpoint is changing my RGB values! christine PowerPoint 0 07-04-2011 09:25 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft