Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel VB to change the value of a cell dependent upon the value of another

Posted on 2016-08-26
9
67 Views
Last Modified: 2016-08-30
I originally posted this as an Excel formula request to see if there was a clever way round without vb, but I don't think it's possible. VBA seems my best bet.

I want to change the contents of a cell depending upon a comparison test, this basically what I am wanting to achieve

if a2=b3 then make j2=g3 otherwise do nothing.

I will actually have a series of these to check (see my attached file). This  spreadsheet details what I want to ultimately achieve, but just the above element of it would be a tremendous help
sample-data-for-experts-exc.xlsx
0
Comment
Question by:Diane Lonergan
  • 4
  • 4
9 Comments
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41772651
It is hard to understand your description on the sheet.
Would you please just populate the rows (after removing the comments from there) as per your logic and then explain the logic behind each row you populated on the sheet?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 41772677
Are you wanting this to happen as a user enters data into the sheet changing those cells or as one of run over it or button someone presses?

Cell checks or amendments are easy enough - I'm using phone at moment so bit looked at your sheet but can be as simple as

if range ("A1").value=range ("A2").value then range ("B1")=range ("A1").value

etc.  you could trigger the VBA when a specific cell or range of cells is changed or being run by button, sheet being saved or whatever us required.

Will look back when on Pc later.

Steve
0
 

Author Comment

by:Diane Lonergan
ID: 41776109
Hi Subodh

I have attempted to illustrate a little better. I hope it makes sense. Thanks
sample-data-for-experts-exc.xlsx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Diane Lonergan
ID: 41776120
Hi Steve

The data will be extracted from another source and pasted into the spreadsheet. The VB would then be executed to run through the spreadsheet and produce the desired results.

Thanks
0
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41776201
Please try this....
In the attached, click the button "Transform Data" on the sheet to run the code and see if this is what you were trying to achieve.

Sub TransformData()
Dim ws As Worksheet
Dim lr As Long, i As Long, n As Long, j As Long, sr As Long
Dim cell As Range
Dim x()
Application.ScreenUpdating = False
Set ws = Sheets("Personnel_Records")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
For Each cell In ws.Range("E:E").SpecialCells(xlCellTypeBlanks).Areas
   If cell.Rows.Count > n Then n = cell.Rows.Count
Next cell
On Error GoTo 0
If n = 0 Then Exit Sub
ReDim x(1 To 1, 1 To n * 2)
For i = 2 To lr
   If sr = 0 Then sr = i
   If ws.Cells(i, 5) = "" Then
      j = j + 1
      x(1, j) = ws.Cells(i, 6)
      j = j + 1
      x(1, j) = ws.Cells(i, 7).Value
   ElseIf j > 0 Then
      ws.Cells(sr, 9).Resize(1, UBound(x, 2)).Value = x
      j = 0
      sr = i
      Erase x
      ReDim x(1 To 1, 1 To n * 2)
   Else
      j = 0
      sr = i
      Erase x
      ReDim x(1 To 1, 1 To n * 2)
   End If
Next i
ws.Columns.AutoFit
ws.Range("E:E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ws.Range("A1").CurrentRegion.Borders.Color = vbBlack
Application.ScreenUpdating = True
MsgBox "Task Completed.", vbInformation
End Sub

Open in new window

sample-data-for-experts-exc.xlsm
0
 

Author Comment

by:Diane Lonergan
ID: 41776297
Subodh

That's perfect thank you so much. You scored a hole in one.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41776301
You're welcome Diane! Glad to help.
0
 

Author Closing Comment

by:Diane Lonergan
ID: 41776302
This was very fast and very helpful
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41776304
Thanks for the feedback.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question