VBA populate target cells getting data and reference from other cells

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
69
Hi,

I hope this is a quick fix for someone with any level of VBA experience.

I have a macro that outputs the active cell reference to cell AM8.
I have formulae that populate a 5x5 grid in cells AY2:BC6 with printed cell references based on the active cell information from cell AM8.
I have another 5x5 grid in cells AS2:AW6 with 0s and 1s.

What I want to have a macro assigned to a button that when clicked would take the 0s and 1s from the cells and AS2:AW6 and populate the target cells referenced in cells AY2:BC6.

A single cell example:

AS2 has "1" in it. AS3 has "0" in it.
AY2 has "$B$2" in it. AY3 has "$B$3" in it.
When I click the button, the macro writes a 1 into cell B2 and a 0 into cell B3.

Thank you in advance to anyone who can help with this.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

kevin9999

Well-known Member
Joined
Aug 28, 2020
Messages
1,017
Office Version
  1. 365
Platform
  1. Windows
It's a bit tricky without seeing a sample of your data using the XL2BB Tool, however, see if this works for you.

VBA Code:
Option Explicit
Sub IndirectCopy()
    Dim i As Long, rng1 As Range, rng2 As Range
    Set rng1 = Range("AS2:AW6")
    Set rng2 = Range("AY2:BC6")
    Application.ScreenUpdating = False
    
    For i = 1 To rng1.Cells.Count
        Range(rng2.Item(i)) = rng1.Item(i)
    Next i
    Application.ScreenUpdating = True
End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
It's a bit tricky without seeing a sample of your data using the XL2BB Tool,
I second that.

However, my submission would look like:
VBA Code:
Public Sub IndirectCopy()
    Dim arrSource As Variant, arrIndirect As Variant
    arrSource = Range("AS2:AW6").Value
    arrIndirect = Range("AY2:BC6").Value
    If UBound(arrSource, 1) = UBound(arrIndirect, 1) And UBound(arrSource, 2) = UBound(arrIndirect, 2) Then
        With Excel.Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            Dim r As Long, c As Long
            For r = LBound(arrSource, 1) To UBound(arrSource, 1)
                For c = LBound(arrSource, 2) To UBound(arrSource, 2)
                    On Error Resume Next
                    Range(arrIndirect(r, c)).Value = arrSource(r, c)
                    On Error GoTo 0
                Next c
            Next r
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
            .ScreenUpdating = True
        End With
    Else
        MsgBox "No equal grid resolution", vbExclamation, "Aborted"
    End If
End Sub
 

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
69
Apologies, I don't have the XL2BB add-on installed on my machine.

Thank you both for taking the time to respond to my question though, all input is appreciated.

I copied each of your codes into a module of VBA, and while it 'sort of' did what I wanted it to do, it didn't do it in the right place.

I did manage to get something working, and it seems to be consistent, but sometimes overwrites the previous location rather than writing to the new location.

Here's what I'm working with at the moment - I'm sure it's not the cleanest or most efficient way of doing what I'm trying to do, but it's the best I have so far:

Code:
Sub Explore()

Application.ScreenUpdating = False

Calculate

'Write the contents of cell AS2 to the cell referenced in cell AY2
Range(Range("AY2")) = Range("AS2")
Range(Range("AY3")) = Range("AS3")
Range(Range("AY4")) = Range("AS4")
Range(Range("AY5")) = Range("AS5")
Range(Range("AY6")) = Range("AS6")

Range(Range("AZ2")) = Range("AT2")
Range(Range("AZ3")) = Range("AT3")
Range(Range("AZ4")) = Range("AT4")
Range(Range("AZ5")) = Range("AT5")
Range(Range("AZ6")) = Range("AT6")

Range(Range("BA2")) = Range("AU2")
Range(Range("BA3")) = Range("AU3")
Range(Range("BA4")) = Range("AU4")
Range(Range("BA5")) = Range("AU5")
Range(Range("BA6")) = Range("AU6")

Range(Range("BB2")) = Range("AV2")
Range(Range("BB3")) = Range("AV3")
Range(Range("BB4")) = Range("AV4")
Range(Range("BB5")) = Range("AV5")
Range(Range("BB6")) = Range("AV6")

Range(Range("BC2")) = Range("AW2")
Range(Range("BC3")) = Range("AW3")
Range(Range("BC4")) = Range("AW4")
Range(Range("BC5")) = Range("AW5")
Range(Range("BC6")) = Range("AW6")

Application.ScreenUpdating = True

End Sub

The workbook is set to calculate manually.
The contents of cells AY2:BC6 are populated using an XLOOKUP referencing data on a separate sheet within the same workbook.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
I copied each of your codes into a module of VBA, and while it 'sort of' did what I wanted it to do, it didn't do it in the right place.
There could be multiple causes for that.
Each of the formulae in the AY-BC grid needs to evaluate to an expected address (or at least a valid one). If it doesn't do that, well ....
Your code relies on VBA's implicitness which is not recommended, since VBA doesn't always return the default member of an object or, to be more specific, the member one would expect.

So this line of implicit code:
VBA Code:
Range(Range("AY2")) = Range("AS2")

should look like:
VBA Code:
Range(Range("AY2").Value).Value = Range("AS2").Value

It would be even better if the Range objects were qualified:
VBA Code:
With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Range("AY2").Value).Value = .Range("AS2").Value
End With

With my simple setup your code and mine output exactly the same, which makes you wonder whether the formulas evaluate as intended.
 

dropkickweasel

Board Regular
Joined
Feb 2, 2014
Messages
69
There could be multiple causes for that.
Each of the formulae in the AY-BC grid needs to evaluate to an expected address (or at least a valid one). If it doesn't do that, well ....
Your code relies on VBA's implicitness which is not recommended, since VBA doesn't always return the default member of an object or, to be more specific, the member one would expect.

So this line of implicit code:
VBA Code:
Range(Range("AY2")) = Range("AS2")

should look like:
VBA Code:
Range(Range("AY2").Value).Value = Range("AS2").Value

It would be even better if the Range objects were qualified:
VBA Code:
With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Range("AY2").Value).Value = .Range("AS2").Value
End With

With my simple setup your code and mine output exactly the same, which makes you wonder whether the formulas evaluate as intended.
Thank you for explaining that.

I 'use' VBA very rarely, and when I do, I tend to copy/paste what I can find that fits what I want to do without really having the time or resources to fully understand it.

I will modify my existing code according to your suggestions to make the code more explicit than implicit.

One quick follow up question;
As I have the same line of code repeated 25 times with different cells being referenced, can I wrap that all inside of the 'With' that you suggest?

So something like:

VBA Code:
Sub Explore()

Calculate

With ThisWorkbook.Worksheets("Sheet1")
    .Range(.Range("AY2").Value).Value = .Range("AS2").Value
    .Range(.Range("AY3").Value).Value = .Range("AS3").Value
'and so on for the other 23 ranges

End With

End sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
As I have the same line of code repeated 25 times with different cells being referenced, can I wrap that all inside of the 'With' that you suggest?
Sure you can, that's the purpose of the With ... End With construct.
 

Forum statistics

Threads
1,176,085
Messages
5,901,295
Members
434,886
Latest member
qazibelal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top