Re: Excel 2003 question: How to update data in a column conditionally

Hi Dan,

Yes, ctl-C to copy, but use the Edit menu and look for Paste Special instead of ctl-V. I think in Excel 2007 it's somewhere on the ribbon bar or whatever they call it.

Glad you figured out the A1 value thing. The c in "for each c in selection" is just a variable name; you could use "for each x in selection" or "for each whatchamacallit in selection". As you said, its definition is implied by the "in selection". Once defined, you can see the properties for it when you type c. in the VBA editor in Excel.

I kind of worked my way up from Basic way back when and evolved into VB and VBA. I learned a lot just from the VB help. With VBA, one way is to record a macro and see what the recorded macro looks like. The other is to try stuff; it's very easy to step through a macro and see what's happening, hovering the mouse cursor over variables to see their values, setting break points, etc.

And there are lots of results when googling for "free vba training"; I have no idea which ones are good or not.

Good luck! And if you get stuck, post a question here. There's usually someone that knows the answer.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

On 11/22/2010 3:07 PM, Dan wrote:

Hi Peter,

Re the one-time deal: I didn't think what was pasted back into the column
would be the derived values, I thought it would just copy the formulas.
When you say copy to clipboard, are you suggesting to select the new column
with formulas, use Ctrl-C, select the column I want to replace, then use
Ctrl-V?

Re the macro: I am seriously lacking in VBA skills. I used the code you
suggested, selected the cells in column J I wanted to modify, but got a
"Run-time error '13': Type mismatch", which is very helpful (not). Whoops,
wait, A1value needs to be defined as an Integer, and this did the trick.
Sweet! Question: Is 'c' (as in "For each c" and "c.offset") a special
representation of the selected cell? Is its definition implied by its use
in the "in Selection"?

Any advice where I can find more information on VBA programming? Preferably
free and online?

Thanks for your help!
- Dan


On Wed, Nov 17, 2010 at 5:47 PM, Peter Dow<petercdow@xxxxxxxxx> wrote:

Hi Dan,

If this is a one time deal, you can temporarily insert a new column and
paste this formula into the rows of that column:

=IF(AND(ISERROR(SEARCH(D1,"SRC3 UUMC PN FCR1")),A1=22),LEFT(J1,2)& "78"
& MID(J1,5,6),J1)

Then select those values and Copy them to the clipboard, and Paste just
the values back to column J, and finally, delete the temporary column.

Or you could write a macro, something like:

Sub FixColJ()

' Conditionally fix values in column J

Dim A1value As String
Dim D1value As String

For Each c In Selection
A1value = c.Offset(0, -9)
D1value = c.Offset(0, -6)
If InStr(D1value, "SRC3 UUMC PN FCR1") = 0 And A1value = 22 Then
c.Value = Left(c.Value, 2)& "78"& Mid(c.Value, 5, 6)
End If
Next
MsgBox "J1 column modified"

End Sub

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx<mailto:pdow@xxxxxxxxxxxxxxx> /

On 11/16/2010 7:36 AM, Dan wrote:

I have a spreadsheet with approx. 500 rows of data. I need to update a
substring in a column conditioned by values in that column and in another
column. If it were SQL, I might use:

Update "worksheet"
set J1 = substr(J1, 1, 2) + '78' + substr(J1, 5, 6)
where D1 not in ('SRC3', 'UUMC', 'PN', 'FCR1')
and A1 = 22

where J1, D1,& A1 are individual cells in the same row (#1); and I need
this repeated for all 500 rows of data. Essentially, I need to replace

the

3rd& 4th character in J1 with '78' when certain conditions are met.

I have no idea how to accomplish this in Excel. I'm guessing a macro is
involved, but my knowledge of them is limited.

Any help or even links to online pointers would be greatly appreciated.

I

googled a bunch of terms, but came up with a lot of external data import,
ODBC type of stuff.

- Dan

--
This is the PC Technical Discussion for iSeries Users (PcTech) mailing list
To post a message email: PcTech@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/pctech
or email: PcTech-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/pctech.