Solved

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

Posted on 2016-08-26
9
62 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 29

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 29

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 29

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 29

Expert Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

810 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