Excel Macro copy to cell based on criteria

[Solved/Closed]
Report
Posts
4
Registration date
Wednesday October 5, 2011
Status
Member
Last seen
October 12, 2011
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
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
Posts
4
Registration date
Wednesday October 5, 2011
Status
Member
Last seen
October 12, 2011

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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
483
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
Posts
4
Registration date
Wednesday October 5, 2011
Status
Member
Last seen
October 12, 2011

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