Excel Macro copy to cell based on criteria

Solved/Closed
megamikeridge Posts 4 Registration date Wednesday October 5, 2011 Status Member Last seen October 12, 2011 - Oct 5, 2011 at 04:41 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 15, 2011 at 07:21 AM
hey everyone,

I'm stuck. I hope someone can help me out today. I have an excel spreadsheet with 1400 rows. The first 3 rows are irrelevant (headings). I have 3 cells with $tring content (B:C:D). What I need to do is create a macro that can specifically look in column B and column D for non-null values. There are multiple cells in this spreadsheet that must have the same value. Column B and D must be compared, and must be found with non-null values. And, in the same instance where Column B is also found again (loop) (same cell content, but different row) Col D will have a null value (missing content). I need to fill this Col D with the same content as the first record that had Col B and D content. In my example the value is hard coded, I don't know how else to do it. I'm very lost and I need some help to complete this. This is what I have.

Sub _MacroUser10()

Set I = Worksheets("Sheet2")
Dim d
Dim j

d = 1 'loop counter
j = 4 'start at the 4th row because of headings

Do Until ActiveCell(I.Range("B" & j) = "User Portfolio Group") And ActiveCell(I.Range("D" & j <> null)) 'loop

If I.Range("B" & j) = "User2010" Then 'the cell is entitled User2010...etc
If I.Range("D" & j) = " " Then 'this is supposed to find the null values in D
d = d + 1
End If
i.Rows(d).Value = I.Rows(j).Value
End If
j = j + 1
Loop

End Sub

All in all. I need to lookup the content in Cell B$ and lookup the content in Cell D$. There will be one row with content in both places. Then I need to find all the other matching rows that match B$ that do not have any values in D$. I need to add those missing values from the non-null D$ to the null D$. I hope that actually makes sense to someone :(

I hope someone can help :) :) :)

3 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Oct 6, 2011 at 08:28 PM
If the case is always that the filled row will occur before blank one , then you can use dictionary object. For that

Loop thru your first row till end
if both cells are not empty, combine the value of B and D as key and value of C as value . as you go thru rows, first check if that key is in the dictionary, if yes, you have the value else add to the dictionary

' to create dic object
Set dicMyDic = CreateObect("Scripting.Dictionary")

'this is how key can be
strKey = B1 & "|" & D1

'check if value of D is blank or not


' to add the value to dic
IF Not (dicMyDic.Exists(strKey )) then dicMyDic.Add Key:=strKey, Item:=val

'to get the value from dic
IF (dicMyDic.Exists(strKey )) then val = dicMyDic(strKey)


Hope that will help
1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Oct 10, 2011 at 09:42 AM
Using a dictionary is completely new for me. I'm still looking and learning about it's possibilities.

The thing I did notice and want to add to Rizvisa's post is that you need to activate it.
Open VB goto Tools > references and select Microsoft Scripting Runtime.

Best regards,
Trowa
0
megamikeridge Posts 4 Registration date Wednesday October 5, 2011 Status Member Last seen October 12, 2011
Oct 12, 2011 at 02:59 PM
Thanks. I am not familiar with the Dictionary scripting either. I'll play with the script and add your suggestions. Thank you @TrowaD and @rizvisa1
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Oct 15, 2011 at 07:21 AM
since the call is like
Set dicMyDic = CreateObect("Scripting.Dictionary")
you do not need to include the reference if you make a call like above. How ever since you indicated that you are not familiar with dictionary object, adding the reference would hep you to see what else you can do with dictionary due to availability of intellisense, when you use the object However in order to use that feature, you would need to change the call as
dim dicMyDic as new Scripting.Dictionary
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Oct 6, 2011 at 10:24 AM
Hi megamikeridge,

Sorry dude, having trouble understanding what you are trying to accomplish. Can you provide a small sample of you sheet before and after running the code (i.e. how it is now and how you want it to be).

You can also use a filesharing site like www.speedyshare.com to upload your file if that is easier.

Best regards,
Trowa
0
megamikeridge Posts 4 Registration date Wednesday October 5, 2011 Status Member Last seen October 12, 2011
Oct 6, 2011 at 04:44 PM
Thank you @TrowaD. Sorry for the confusion. Enclosed is a template.
http://www.4shared.com/file/KmZ4QWy-/Workbook1.html

In Sheet 1 is what the values look like
In Sheet 2 is what all the values should look like.

In short, in sheet1 there are missing values for various rows (col D mainly). I need to create a copy + paste macro that can search through the rows (columns B&D) that are complete AND search through the other rows that have Col B (with values) and Col D (null). When the macro finds a match, it can copy + paste the values from the complete row (column B & D) into the incomplete row (Column D).

There are multiple ways to create this, it seems as though I've picked the wrong way, can't figure it out today. Code is buggy. Any help is appreciated. None is ok too :( :( thanks
0