Results 1 to 7 of 7

Thread: Edit and Refresh of cells in VB

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2008
    Posts
    3

    Edit and Refresh of cells in VB

    How can edit and refresh of a cell in excel by using VB

  2. #2
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Edit and Refresh of cells in VB

    VERY basically ...

    Refresh:

    ActiveSheet.Calculate

    Edit:

    Range("B5").Value = Range("B5").Value + 27

    Let us know a little more specifically what you are trying to do.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  3. #3
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Edit and Refresh of cells in VB

    The easiest way to get started is to Record a Macro or two to do some simple things.

    Menu/Tool Bar: Tools > Macro > Record New Macro
    then
    Tools > Macro > Stop Recording

    Now, you can open the macro in VBA and examine what you did ...

    Tools > Macro > Macros > Select one of the Macros and click "Edit".

    This opens the VBA (Visual Basic for Applications) Editor so that you can see the code that was generated. This is where you write your own custom code to cause Excel to do what you want. This is where you would put the commands from my earlier post.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  4. #4

    Thread Starter
    New Member
    Join Date
    Sep 2008
    Posts
    3

    How can we Edit and Refresh a cell in VB

    Quote Originally Posted by bhaskarababu99
    How can edit and refresh of a cell in excel by using VB
    Hi, I am unable to get the exact answer from this forum.
    I would like to give the exact problem , so that it will be more helpful to analyze the solution.

    I have worksheet which is having two columns and around 2000 rows.In each and every row first column has a parameter and the second column has value associated with that parameter.
    These parameters are only a limited number, the 50 parameters only repeating randomly among the 2000 rows.

    My issue is , I need to change the value(second column) of a parameter in all the places in the entire list.It has to refer the first column for particular parameter( let us say "X") and change its value in the entire list when ever it finds the parameter any where in the list.

    It needs to do by using VBA only.

  5. #5
    Addicted Member
    Join Date
    Sep 2008
    Posts
    128

    Re: Edit and Refresh of cells in VB

    can u give us a sample of the data, specify the variables ur using and explain how ur assigning the variables in question, it may give us some insight into what ur trying to do?

  6. #6
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Edit and Refresh of cells in VB

    How do you intend to specify the particular value(s) for the particular parameter(s) that you want to change? There is no problem scanning any number of rows to change the value matched to particular parameter.

    Please give us an example of a parameter in column A and the value for that parameter that you want to set in column B.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  7. #7
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Edit and Refresh of cells in VB

    This is a simple example ... you can put your PARAMETER and New Value in the Global constants ... If you change "Integer" to "Variant" you can put anything in PARAMETER and NEW_VALUE. This will search all Parmeters and load the new value into the parameter of interest ...
    Code:
    Option Explicit
    
    'Global CONSTANTS:
    '================
    'Parameter to search for
    Const PARAMETER As Integer = 1234
    
    'Value to set for the above parameter
    Const NEW_VALUE As Integer = 4321
    
    'Beginning Row for Scan
    Const bRow As Long = 1
    
    
    Sub Replace_Value_for_Parameter()
        Dim aRow As Long  'Row Iterator
        Dim eRow As Long  'Ending Row for Scan
        
        'Find the last occupied cell in Column A
        eRow = Range("A65536").End(xlUp).Row
        'Scan all occupied parameters for the PARAMETER of interest
        For aRow = bRow To eRow
            'Check for the parameter
            If Cells(aRow, "A").Value = PARAMETER Then
                'This is an instance of PARAMETER ... set the new value
                Cells(aRow, "B").Value = NEW_VALUE
            End If
        Next aRow
    
    End Sub
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width