Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-08-26
9
Medium Priority
?
76 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
[X]
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
  • 4
  • 4
9 Comments
 
LVL 32

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 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 32

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 32

Expert Comment

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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

722 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