Macro in Excel (1 Viewer)

[Zippyfrog]

Hi, I am trying to create a macro in excel to clean up data that I export from another program. What I did is I recorded a macro, and the macro works beautifully for the set of data that day. However, the next day when I export, the number of rows is different, so the recorded macro I have that has excel cells and rows doesn't work.

What I am trying to accomplish is two things:

1) Delete all rows that have data but don't have a value in column B. The way I did it was sort by column B then deleted all rows that had a blank column B. The first day there were 20 rows, then next day there were 43 rows, so you can see the issue of the recorded macro where it only deleted 20 rows.

2) I want to do an autofill for all the remaining rows with a new column of data. Once again, the recorded macro had a different number of rows then the next day's data. So I would love to only autofill down on a row that has data somewhere else in the row.

Any advice on how to do this would be greatly appreciated!

 

[namliam]

1)
Something along the lines of:

Sub x()
    Selection.CurrentRegion.Select
    For r = Selection.Rows.Count To 1 Step -1
        If Cells(r, 2) = "" Then
            Rows(r).Delete
            r = r - 1
        End If
    Next r

2) What do you want to be autofilled? you can adjust the above to fill it for you in an else of that if... something like:

Else
Cells(r,1) = "Namliam" 
endif

to fill the current row in column 1 (aka A)

 

[Zippyfrog]

Thank you for that information. I do have two more questions, and maybe I misunderstood your previous post. What I want to have an autofill down in column A that says "2013-2014", so here is the code that I used. However, when I did that, it only filled in the even cells. Can you see what I did wrong?

    Selection.CurrentRegion.Select
    For r = Selection.Rows.Count To 1 Step -1
        If Cells(r, 1) = "" Then
            Cells(r,1) = "2013-2014"
            r = r - 1
        End If
    Next r

Second, I have a full column of data that comes out of my program that comes out as a decimal, such as 26.57, and I want every row for this column to be rounded up to the nearest whole number, in this case 27. The closest thing I came up with was using Selection.NumberFormat = "0" however that only changes the appearance - if I put my cursor over the data when the macro is done running, the data still shows the decimal.

 

[namliam]

2) What do you want to be autofilled? you can adjust the above to fill it for you in an else of that if... something like:

Else
Cells(r,1) = "Namliam" 
endif

to fill the current row in column 1 (aka A)

Try reading it again, just to stop me from repeating myself. :banghead:

Yes its obvious if you change only the formatting you dont change the value.....
Something along these lines should round your actual values.
Cells(r,c) = round(cells(r,c),0)

 

[Zippyfrog]

Try reading it again, just to stop me from repeating myself. :banghead:

Yes its obvious if you change only the formatting you dont change the value.....
Something along these lines should round your actual values.
Cells(r,c) = round(cells(r,c),0)

Sorry for the first part with the banging of the head. I forgot to explain something - this is the code that I used originally based off of what you shared

    Selection.CurrentRegion.Select
    For r = Selection.Rows.Count To 1 Step -1
        If Cells(r, 2) = "" Then
            Rows(r).Delete
            r = r - 1
	Else
	Cells(r,1) = "Regular" 
        End If
    Next r

The issue is that every time a row was deleted, then the word Regular was not put in the row above it. I put breakpoints in, and let's say that row 12 had a value in column B, row 13 did not, row 14 did. This code works from the bottom up, so when it got to row 13, it deleted it, but then it did not put "regular" in Row 12, rather it jumped right to row 11.

So that is why I tried the other way. Hope that makes a little more sense and you can give me some more insight

 

[Zippyfrog]

Actually, I think I found the issue - it appears to work if I change the r = r-1 line to just r = r.

 

[Zippyfrog]

I do have one more question - how would I use the following formula inside of an excel macro?

TRIM(LEFT(D2,FIND("~~",SUBSTITUTE(D2,"-","~~",LEN(D2)-LEN(SUBSTITUTE(D2,"-",""))))-1))

This is what I use in my Excel Spreadsheet and am trying to automate it.

I tried this:

For r = Selection.Rows.Count To 2 Step -1
Cells(r,4) = TRIM(LEFT(Cells(r,4),FIND("~~",SUBSTITUTE(Cells(r,4),"-","~~",LEN(Cells(r,4))-LEN(SUBSTITUTE(Cells(r,4),"-",""))))-1))
Next r

However, it tells me that the second 'substitute' is not a valid sub procedure. I went and manually used the formula in an excel spreadsheet by using exact cells references and the formula works with no problem.

 

[namliam]

The R=R - 1 is a remnance from my original do while loop I think, no need for it at all with the For Next loop

I think the use of the formula is this syntax...
Cells().formula = "=formula"
Is it your goal to use the actual formula? Or to write the resulting text? Why using vba anyways, I ussually find writing the actual value is best.

 

[Zippyfrog]

I think the use of the formula is this syntax...
Cells().formula = "=formula"
Is it your goal to use the actual formula? Or to write the resulting text? Why using vba anyways, I ussually find writing the actual value is best.

My goal is to get the output in the cell. Our current program throws an extra hyphen plus some random number at the back of the string, and I need that hypen plus some random number stripped off. And the value that I need cleaned is in column D, so what I do is I put the formula

TRIM(LEFT(D2,FIND("~~",SUBSTITUTE(D2,"-","~~",LEN(D2)-LEN(SUBSTITUTE(D2,"-",""))))-1))

in a column off the page, say column P, and I fill down to the end of the data. Then I copy and paste Column P which has the data with the hyphen and random number stripped off, back into column D using the "paste values" option. Then I go to column P and delete. A very roundabout way for just trying to strip off these values on the back.

My goal in using this VBA macros is so that I can hand this project off to someone else who can just run the macro each time.

 

[namliam]

Well in that case dont use the "in sheet" functions but rather the VBA functions
Left
Instr
Replace
Len

 

[Zippyfrog]

Well in that case dont use the "in sheet" functions but rather the VBA functions
Left
Instr
Replace
Len

So, if I understanding you right, would my function look like this?

Cells(r,4) = trim(left(Cells(r,4),Instr("~~",Replace(Cells(r,4),"-","~~",len(Cells(r,4))-len
(Replace(Cells(r,4),"-",""))))-1))

I tried that and I get an invalid procedure.

 

[namliam]

Invalid procedure propably means you are screwing up atleast one of the functions someplace....

For example....
Len(Cells(r, 4)) - Len(Replace(Cells(r, 4), "-", ""))
If this part becomes Zero, you get the Replace that looks like:
Replace(Cells(r, 4), "-", "~~", 0)
That zero there will cause the error you mention
Which will basicaly happen when there is no "-" to replace, adding a +1 behind it may fix the immediate problem but I dont know how that may affect your compound function....

The listed functions are the proper ones to use, just a matter for you to find out how to properly use them (or give us a list of possible values that you may encounter and I can try and figure it out for you)
The power of VBA though is you dont need to do it all in one line like you do formula's in cells....
You can do something like:

IF this = that then
    do this
else
    do that
endif

much much easier than you can do with IF in the inline formula's
Keep in mind though that the formula's do NOT translate in use 1:1 to VBA, instead assume you know nothing and read the Help on each function in detail to find out how to fill it.

 

[Zippyfrog]

Here is a sample from my data last week. Working at a school, these represent courses and the last "space hyphen space number" in very back needs to be deleted. And just the very last space hype space number needs to removed.

BBL 12 CLTR - 1
BSN ECON/PER - 2
ENG AP ENG 1 - 4
MTH APCALC-1 - 3
SCI ANATADV1 - 1
SCI APCHEM-1 - 7
BBL 12 CLTR - 3
FA CHOIR-1 - 6
FA DRAW I - 2
FA MUSTHCMP - 4
PE1314 DANC - 1
SS GOVT - 7
BBL 12 DOC - 4
BBL 12 CLTR - 1

So this is what I am hoping to accomplish through VBA:
BBL 12 CLTR
BSN ECON/PER
ENG AP ENG 1
MTH APCALC-1
SCI ANATADV1
SCI APCHEM-1
BBL 12 CLTR
FA CHOIR-1
FA DRAW I
FA MUSTHCMP
PE1314 DANC
SS GOVT
BBL 12 DOC
BBL 12 CLTR

 

[namliam]

So with all due respect by why would you replace the - by ~~ ???

As per your sample data, why not simply do:

For r = 1 To 14
    ' Simple solution, simply cut off last 4 characters
    Cells(r, 4) = Left(Cells(r, 1), Len(Cells(r, 1)) - 4)
    ' Based of last "-"
    Cells(r, 5) = Trim(Left(Cells(r, 1), InStrRev(Cells(r, 1), "-", -1) - 1))
Next r

 

[Zippyfrog]

So with all due respect by why would you replace the - by ~~ ???

As per your sample data, why not simply do:

For r = 1 To 14
    ' Simple solution, simply cut off last 4 characters
    Cells(r, 4) = Left(Cells(r, 1), Len(Cells(r, 1)) - 4)
    ' Based of last "-"
    Cells(r, 5) = Trim(Left(Cells(r, 1), InStrRev(Cells(r, 1), "-", -1) - 1))
Next r

Oh my goodness, thank you so much! That second example is exactly what I was looking for! Although my data doesn't show it, sometimes the numbers are - 13 or larger numbers, so always going off of that last hypen is exactly what I needed to have happen. I don't remember the reasoning for the ~~ part, all I remember is that it made it work and so I went with it. But the way you write that small bit of code is so eloquent and easily understood! Thanks again!

 

[namliam]

Although my data doesn't show it, sometimes the numbers are - 13 or larger numbers

When giving sample data on forums when asking for help, make sure it includes all your different possibilities... I was going to give you the first solution only and then GAMBLED that you needed a bit more....