The code isn't working

MazExpress

Board Regular
Joined
Aug 5, 2020
Messages
53
Office Version
  1. 2010
Platform
  1. Windows
I have a monthly sheet (Aug. Profits) that has 2 columns of concern, F & G. How to copy the values in column G to another workbook (Actual) in the column of the current month?
Knowing that:
1- The order of names in the first sheet is different from month to another.
2- Sometimes there are no names available in column F, so the corresponding values are useless.
3- Some names and values maybe unwanted. In other words, I want to only copy the values in column G which have names in the second sheet.

A very helpful member of the forum had suggested this code:

Code:
Sub CopyValues()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, Val As String, arr1 As Variant, arr2 As Variant, i As Long, fnd As Range
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = Workbooks("Actual.xlsx").Sheets("Sheet1")
    Set fnd = WS2.Rows(1).Find(Left(MonthName(Month(Date)), 3))
    If Not fnd Is Nothing Then
        arr2 = WS2.Range("A2", WS2.Range("A" & WS2.Rows.Count).End(xlUp))
        arr1 = WS1.Range("F2", WS1.Range("F" & WS1.Rows.Count).End(xlUp)).Resize(, 2).Value
        Set RngList = CreateObject("Scripting.Dictionary")
        For i = LBound(arr1) To UBound(arr1)
            Val = arr1(i, 1)
            If Val <> "" Then
                RngList.Add Key:=Val, Item:=arr1(i, 2)
            End If
        Next i
        For i = LBound(arr2) To UBound(arr2)
            Val = arr2(i, 1)
            If RngList.exists(Val) Then
                WS2.Cells(i + 1, fnd.Column) = RngList(Val)
            End If
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

The code is working properly with him and fulfilling my needs. When copying the same code to a new module on my setup, it didn't work at all !! N.B Both of us are using Windows 10 and Office 2010.

Any help ??
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
'Not working' is a very ambiguous term when it comes to excel. Does nothing? Does the wrong thing? If so, what is it doing wrong? Runtime error? If so what error code and which line?

Is "Sheet1" the correct sheet name in both workbooks?
 

MazExpress

Board Regular
Joined
Aug 5, 2020
Messages
53
Office Version
  1. 2010
Platform
  1. Windows
I mean it does nothing. Everything stays as it is. No changes and no errors.

Yes, "Sheet1" is the correct sheet name in both workbooks.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
Works fine for me, I had to use the Aug sheet from @mumps post in the original thread as the one that you attached here has an invalid file extension.
Try this test code to see if it is picking up the august column correctly. It will either show a message box saying $I$1 or a runtime error.
VBA Code:
Sub findmonth()
MsgBox Workbooks("Actual.xlsx").Sheets("Sheet1").Rows(1).Find(Left(MonthName(Month(Date)), 3)).Address
End Sub

I'm not entirely sure what will happen if the scripting dictionary is not available to you, although I would expect an error rather than simply nothing.
 

MazExpress

Board Regular
Joined
Aug 5, 2020
Messages
53
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

error.JPG
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
That means that it's not finding Aug in the cell, best guess is that your sheet contains dates formatted as MMM while the code is looking for a text string representing the month.

If you double click into I1 (or whichever cell contains 'Aug' in your sheet) does it stay as Aug, or does it change to a date in the format of 01/02/2020?
 

MazExpress

Board Regular
Joined
Aug 5, 2020
Messages
53
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

It stays as Aug.
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Have you set the Office Scripting on the other machine as you mentioned when you copy the module to your setup. In VBA Select the Tools Menu and Reference and search down for Microsoft Scripting Runtime
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,945
Office Version
  1. 365
Platform
  1. Windows
That code uses late binding, so no references are needed.

@MazExpress
Does the code work for you on the test files?
 

MazExpress

Board Regular
Joined
Aug 5, 2020
Messages
53
Office Version
  1. 2010
Platform
  1. Windows
I've checked that box, but nothing different has happened when I ran the macro again! No changes and no errors.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,275
Messages
5,641,250
Members
417,202
Latest member
AndyVBA

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